Condition on preceding years (assigning dummy variable)

[Fanetti Mazakura]

Dear all,

I need to assign a dummy variable equal to one if a particular person has made at least one trade in each of the two preceding years. I tried several approaches, but with no luck.

Here's a chunk of my dataset:

* Example generated by -dataex-. To install: ssc install dataex
clear
input long personid double(permno tprice) float month
12058108 76892    16.5 482
12058108 76892   31.75 499
12058108 76892   31.25 502
12058108 76892   34.15 508
12058108 76892    36.5 521
12058108 76892      39 526
12058108 76892   39.03 527
12058108 76892   40.02 529
12058108 76892   40.58 530
12058108 76892   41.75 531
12058108 76892   46.35 538
12058108 76892   48.03 539
12058108 76892    47.1 540
12058108 76892   42.64 544
12058108 76892   43.44 550
12058108 76892   47.29 551
12058108 76892   44.83 553
12058108 76892   50.34 562
12058108 76892  53.162 563
12058108 76892   53.61 568
12058108 76892 52.6819 573
12058108 76892   52.89 574
12058108 76892   53.31 575
end
format %tmMon_CCYY month

I would really appreciate any help!

Cheers

[Clyde Schechter]

The first step is to extract the year from the month variable. Then the key step is to use the -rangestat- command, by Robert Picard, Nick Cox, and Roberto Ferrer, available from SSC, to determine whether there are purchases in the past 1 year, and again 2 years ago. Then just combine with &.

gen year = yofd(dofm(month))

forvalues lag = 1/2 {
    rangestat (count) bought_`lag'_yrs_ago = tprice, by(personid) interval(year -`lag' -`lag')
    mvencode bought_`lag'_yrs_ago, mv(0)
}

gen byte wanted = bought_1_yrs_ago & bought_2_yrs_ago

Note: In your example data, there is only one person, and that person has purchases in every year, so his/her result is always 1, except for the first two years where there is simply no data to refer back to.

[Fanetti Mazakura]

Thank you very much, Clyde!!! Helpful as always!

What if I want to assign the dummy variable equal to one for all of the observations in which the person has traded three consecutive years in a row? For instance to assign wanted=1 for the first two years where there is no data to refer back to. How do I do that?

[Clyde Schechter]

forvalues lag = 1/3 {
    rangestat (count) bought_`lag'_yrs_ago = tprice, by(personid) interval(year -`lag' -`lag')
    mvencode bought_`lag'_yrs_ago, mv(0)
}

gen byte wanted = bought_1_yrs_ago & bought_2_yrs_ago & bought_3_years_ago

by personid (year), sort: replace wanted = 1 if year - year[1] < 3