Dear All,
I was trying to create a new variable ， called lag_ar.
here is my original dataset:
| PERMNO | EVTDATE | EVTTIME | cumulative_ar | program_code | counter_overall |
| 10001 | 8/1/11 | 5 | 0.933284 | 2 | 1 |
| 10001 | 4/2/12 | 5 | 0.98016 | 2 | 2 |
| 10002 | 7/6/98 | 5 | 1.14444 | 2 | 1 |
| 10002 | 10/27/98 | 5 | 0.947167 | 2 | 2 |
| 10020 | 4/6/87 | 5 | 1.003912 | 3 | 1 |
| 10020 | 10/21/87 | 5 | 0.898007 | 3 | 2 |
| 10020 | 12/11/87 | 5 | 1.032888 | 3 | 3 |
| 10020 | 4/17/90 | 5 | 1.023325 | 2 | 1 |
| 10020 | 4/24/90 | 5 | 1.063576 | 2 | 2 |
my code is
proc sort data = datasetname;
by permno evtdate program_code;
run;
data within_lag;
set within;
by permno evtdate program_code;
cum_ar_lag1 = ifn(not(first.permno),lag(cumulative_ar),.);
run;
but with my code, the result is not right;
take permno 10020 for example,
my code gives the result as below:
| 10020 | 4/6/87 | 5 | 1.003912 | 3 | 1 | |
| 10020 | 10/21/87 | 5 | 0.898007 | 3 | 2 | 1.003912 |
| 10020 | 12/11/87 | 5 | 1.032888 | 3 | 3 | 0.898007 |
| 10020 | 4/17/90 | 5 | 1.023325 | 2 | 1 | 1.032888 |
| 10020 | 4/24/90 | 5 | 1.063576 | 2 | 2 | 1.023325 |
however, for the 4th obs, the lag_ar should be ".", but it still grab the value from the 3rd observation. however, they are not in the same program code, which it should not grab the value and assign it there.
the right result I expect is as below:
the lag is supposed to do within same permno and same program_code.
| PERMNO | EVTDATE | EVTTIME | cumulative_ar | program_code | counter_overall | lag_ar |
| 10020 | 4/6/87 | 5 | 1.003912 | 3 | 1 | |
| 10020 | 10/21/87 | 5 | 0.898007 | 3 | 2 | 1.003912 |
| 10020 | 12/11/87 | 5 | 1.032888 | 3 | 3 | 0.898007 |
| 10020 | 4/17/90 | 5 | 1.023325 | 2 | 1 | |
| 10020 | 4/24/90 | 5 | 1.063576 | 2 | 2 | 1.023325 |
I know I can fix my wrong result by assign a missing value to all obs that counter_overall is 1, but I really want to figure out why my
by statement does not work.
Thank you so much again.
Zhongda
Click image to register for webinar
