lag variable with by function of two parameters

Zhongda — Tue, 08 Nov 2022 02:52:22 GMT

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

Re: lag variable with by function of two parameters

Kurt_Bremser — Tue, 08 Nov 2022 06:26:52 GMT

It looks like you should use first.program_code in place of first.permno.

topic Re: lag variable with by function of two parameters in SAS Procedures

lag variable with by function of two parameters

Re: lag variable with by function of two parameters