Creating forward values in cross-sectional data

[Torben Lieber]

Dear Stata-Listers,

I have the feeling that the solution is quite easy. However, I just can't figure it out.

So, I have cross sectional data for regional districts in Germany with differing observations per year, for the years 2012 to 2015.

The variable A is asked in the years 2014 and 2015 only.

Much like so:

A	year	district
.	2012	1
.	2012	2
.	2013	3
.	2013	1
212	2014	1
123	2014	2
234	2014	3
345	2015	1
123	2015	2
235	2015	2
345	2015	3

When:

reg A year

I get beta=72.3

What I would like to do now is set variable A in the year 2013 to A(year=2014)-72.3 for each district separately.

The tsset command does not work, which is why I can't work with forward values, like:
by district: A = F.A - 72.3 if year < 2015

Is there a simple solution to my problem?


I would very much appreciate any help!

With kind regards
Torben

[Marcos Almeida]

Since you have aproximately 2 dozens of posts in this forum, I assume you know that the best approach to get code is providing data to work on.

If I understood this query correctly, you wish to impute missing data.

Stata has a nice machinery to perform this task, and for this you may type: help mi.

I didn't get the reason for replacing the value of Yvar for the one in the next year minus the coefficient from the complete-cases analysis.

That being said, you may also take a look at the time-series operators, and see whether they are helpful to you. I'm saying this more so because of the title of the query, not exactly its content.

[Torben Lieber]

Thank you Marcos,

I understand your point and hope that my previous edit could help making things clear.

Best regards,
Torben

[William Lisowski]

The simplest solution to your stated problem is similar to the code you have suggested.

bysort district (year): replace A = F.A - 72.3 if year<2015

Your actual problem is that your attempt to tsset your data has failed. Why is that? In the sample data in post #1, you have two observations of district 2 in 2015, and none in 2013. In the code below, I have changed the data for district #2. In your real data, you need to understand why tsset is not working and fix that problem.

* Example generated by -dataex-. To install: ssc install dataex
clear
input int(A year) byte district
  . 2012 1
  . 2013 1
212 2014 1
345 2015 1
  . 2012 2
123 2013 2 (was 2014 in post #1)
123 2014 2 (was 2015 in post #1)
235 2015 2 
  . 2013 3
234 2014 3
345 2015 3
end

sort district year
tsset district year
generate old_A = A
by district (year): replace A = F.A - 72.3 if year < 2015
list district year old_A A, sepby(district) noobs

. list district year old_A A, sepby(district) noobs

  +---------------------------------+
  | district   year   old_A       A |
  |---------------------------------|
  |        1   2012       .       . |
  |        1   2013       .   139.7 |
  |        1   2014     212   272.7 |
  |        1   2015     345     345 |
  |---------------------------------|
  |        2   2012       .    50.7 |
  |        2   2013     123    50.7 |
  |        2   2014     123   162.7 |
  |        2   2015     235     235 |
  |---------------------------------|
  |        3   2013       .   161.7 |
  |        3   2014     234   272.7 |
  |        3   2015     345     345 |
  +---------------------------------+