Variable generating

[Josh Wang]

Dear Statalister,
I have a sample, the example is followed. id is the firm.
I want to generate a categorical variable X, if the firm has consecutive sale since the first year it has sale, it is coded as 2 (such as my example data id=1 and id=2). If the firm start to have sales but in the following year it stop, or suspend in several years, it is coded as 1.
I'm looking for the syntax that can help to generate this categorical variable X.

Thank you.

Best,
Josh

----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(id year sale X)
1 2001 100 2
1 2002 121 2
1 2003 123 2
1 2004 131 2
1 2005 123 2
2 2001   0 2
2 2002   0 2
2 2003 134 2
2 2004 145 2
2 2005 145 2
3 2001 134 1
3 2002 123 1
3 2003 143 1
3 2004   0 1
3 2005   0 1
4 2001 121 1
4 2002   0 1
4 2003 124 1
4 2004   0 1
4 2005 131 1
end

------------------ copy up to and including the previous line ------------------

[Daniel Schaefer]

I think this should work:

clear
input float(id year sale X)
1 2001 100 2
1 2002 121 2
1 2003 123 2
1 2004 131 2
1 2005 123 2
2 2001   0 2
2 2002   0 2
2 2003 134 2
2 2004 145 2
2 2005 145 2
3 2001 134 1
3 2002 123 1
3 2003 143 1
3 2004   0 1
3 2005   0 1
4 2001 121 1
4 2002   0 1
4 2003 124 1
4 2004   0 1
4 2005 131 1
end

sort id
gen indicator = (sale[_n-1] > 0) & (sale[_n] == 0) & id[_n-1] == id[_n]
by id: egen total = total(indicator)
by id: gen wanted = total > 0
replace wanted = 2 - wanted
drop indicator total
list, clean noobs

. list, clean noobs

    id   year   sale   X   wanted  
     1   2001    100   2        2  
     1   2002    121   2        2  
     1   2003    123   2        2  
     1   2004    131   2        2  
     1   2005    123   2        2  
     2   2001      0   2        2  
     2   2002      0   2        2  
     2   2003    134   2        2  
     2   2004    145   2        2  
     2   2005    145   2        2  
     3   2001    134   1        1  
     3   2002    123   1        1  
     3   2003    143   1        1  
     3   2004      0   1        1  
     3   2005      0   1        1  
     4   2001    121   1        1  
     4   2002      0   1        1  
     4   2003    124   1        1  
     4   2004      0   1        1  
     4   2005    131   1        1