Retain last value in panel data

[Fadzai Chikwava]

Please advise syntax for retaining last value, time variable is "fin_year" and I would like the most recent "last_place_type". Also since there is missing data; ignore missing data and take the last recorded "last_place_type". I tried this and it did not work:

by personid (fin_year), sort: gen last_ptype = last_place_type[_N] if last_place_type !=.

[CODE]
* Example generated by -dataex-. To install: ssc install dataex
clear
input long personid str7 fin_year long last_place_type
2337 "FY10/11" 5
2337 "FY11/12" 5
2337 "FY11/12" .
2337 "FY12/13" .
3172 "FY09/10" .
3172 "FY09/10" 5
3172 "FY09/10" .
3172 "FY09/10" .
3172 "FY09/10" .
3172 "FY10/11" .
3172 "FY10/11" .
3172 "FY10/11" 8
3172 "FY10/11" .
3172 "FY10/11" .
3172 "FY11/12" 8
3172 "FY12/13" 8
3172 "FY13/14" .
3653 "FY07/08" 4
3653 "FY08/09" 4
3653 "FY09/10" 4
3653 "FY10/11" 4
3653 "FY11/12" 4
3653 "FY12/13" 4
3653 "FY13/14" .
5625 "FY08/09" .
5625 "FY08/09" .
5625 "FY09/10" .
5625 "FY09/10" .
5625 "FY10/11" .
5625 "FY10/11" .
5625 "FY11/12" 5
5625 "FY12/13" 5
10045 "FY07/08" 4
10045 "FY08/09" 4
10045 "FY09/10" 4
10045 "FY10/11" 4
10045 "FY11/12" 4
10045 "FY12/13" 5
10045 "FY13/14" .

[Rhys Williams]

I'm not at my desktop to check but I think you have misspecified the by command:

bysort personid (fin_year): gen last_ptype = last_place_type[_N] if last_place_type

[Clyde Schechter]

gen byte nm_lpt = !missing(last_place_type)
by personid (nm_lpt fin_year), sort: gen most_recent_lpt = last_place_type[_N]

And if you want, you can restore the original sort order afterwards with -sort personid fin_year-.

Added: Crossed with #2 which, if I understand what is wanted, is not correct in that it will produce missing value results if last_place_type happens to be missing in the last year observed in the data for that person id.

[Fadzai Chikwava]

Thank you very much. I have another similar problem (data in wide format) which has some missing values and I want to compute a new variable which is:
gen newwvar = ((tot_wethos_1rough90*13) + (tot_wethos_2emerg90*12) + (tot_wethos_3temp90*11) + (tot_wethos_6institution90*8)) * n_moves90

but I am getting missing data.


----------------------- copy starting from the next line -----------------------
[CODE]
* Example generated by -dataex-. To install: ssc install dataex
clear
input long personid byte(tot_wethos_1rough90 tot_wethos_2emerg90 tot_wethos_3temp90 tot_wethos_6institution90 n_moves90)
2337 . . 2 . 4
3172 . . 10 . 1
5625 . 2 1 . 2
10045 . . . . 1
11758 3 1 3 . 5
14193 . 1 . . 2
17206 . . . . 1
17353 . 1 2 . 4
19252 . . 2 . 1
19949 . . . . 3
22038 5 . . . 1
23662 . . 2 . 1
24787 . . . . 1
26141 . . . . 1
26866 . . 1 . 2
27533 . . . 1 3
28539 2 . . 1 6
28548 10 1 . . 3
28818 1 2 . . 4
29159 . . . . 1
29476 3 1 . 1 4
29518 1 . 1 1 4
29716 . . . 1 2
30077 . . 3 . 1
30100 2 . . . 3
30742 7 . 1 . 2
33589 . . . 1 3
33847 . . . . 1
33914 . . 1 . 2
35578 . . . 1 1
40249 1 . 3 . 4
40610 1 . 4 . 3
41482 . . 1 . 3
42526 . . . . 2
43013 . . . 1 1
53436 . . . . 1
54282 1 . 6 . 4
54520 . . . 1 2
54564 . . . 1 3
54845 . 1 . . 2

[Clyde Schechter]

-help egen-. You want the -rowtotal()- function. You will have to create separate variables that multiply the original ones by 13, 12, 11, etc. first.

Alternatively, you can reshape the data to long layout. For most purposes long data is easier to work with in Stata anyway, although it depends on what you will be doing with these variables later.

[Fadzai Chikwava]

Thank you. Seemed to work by converting the missing values to zeros.

[Clyde Schechter]

It doesn't convert the missing values to zero: the variables being added all retain their original values, missing or otherwise. What it does is treat the missing values as if they were zero when adding them together.

[Fadzai Chikwava]

ok, this clarifies

[Nick Cox]

An equivalent wording, which I slightly prefer, is that rowtotal() ignores missings. (What else could it do? There is an alternative that it returns missing, and there is a logic to that, and outside Stata it's often default behaviour. If asked the total of 42, 666 and missing, "708, ignoring the missing which we can do nothing about" and "don't know, that is say missing, as we have no idea what the missing means" are both defensible answers.)