I have a dataset that looks like this in Stata:
| Place | year | value |
|---|---|---|
| a | 2000 | |
| b | 2000 | |
| c | 2000 | 100263 |
| a | 2001 | 100261 |
| b | 2001 | 100262 |
| c | 2001 | 100263 |
| a | 2002 | |
| b | 2002 | |
| c | 2002 |
I want the values to remain the same but be matched to a,b,c to all years so that it looks like:
| Place | year | value |
|---|---|---|
| a | 2000 | 100261 |
| b | 2000 | 100262 |
| c | 2000 | 100263 |
| a | 2001 | 100261 |
| b | 2001 | 100262 |
| c | 2001 | 100263 |
| a | 2002 | 100261 |
| b | 2002 | 100262 |
| c | 2002 | 100263 |
and if there is any place observation d that does not match anything, I would like it to return an NA. I tried using the append command. but that won't work. Do you have any tips on how to do this?
One solution is to use stripolate from SSC.
* Example generated by -dataex-. For more info, type help dataex
clear
input str1 place int year str6 value
"a" 2000 ""
"b" 2000 ""
"c" 2000 100263
"a" 2001 100261
"b" 2001 100262
"c" 2001 100263
"a" 2002 ""
"b" 2002 ""
"c" 2002 ""
end
sort place year
stripolate value year, groupwise by(place) gen(value2)
l , sepby(place)
+--------------------------------+
| place year value value2 |
|--------------------------------|
1. | a 2000 100261 |
2. | a 2001 100261 100261 |
3. | a 2002 100261 |
|--------------------------------|
4. | b 2000 100262 |
5. | b 2001 100262 100262 |
6. | b 2002 100262 |
|--------------------------------|
7. | c 2000 100263 100263 |
8. | c 2001 100263 100263 |
9. | c 2002 100263 |
+--------------------------------+
Note that a corresponding solution for numeric variables would be
mipolate value year, groupwise by(place) gen(value2)
where mipolate is also from SSC.