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
It looks like you should use first.program_code in place of first.permno.
SAS is headed back to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team.
Interested in speaking? Content from our attendees is one of the reasons that makes SAS Innovate such a special event!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.