topic Re: Compute 2 lag values in SAS Programming

Compute 2 lag values

bd_user_10 — Mon, 10 Sep 2018 11:45:35 GMT

Hi Everyone,

I have the following dataset

Date	ISIN	month	OIB
3/01/2010	FI0009000053	-1	1.7933
4/01/2010	FI0009000053	-1	1.7933
6/02/2010	FI0009000053	-2	6.6921
7/02/2010	FI0009000053	-2	6.6921
17/01/2001	FI0009000145	-1	-7.6491
18/01/2001	FI0009000145	-1	-7.6491
21/02/2001	FI0009000145	-2	.
22/02/2001	FI0009000145	-2	.

and I want the following

Date	ISIN	month	OIB	lag1	lag2
3/01/2010	FI0009000053	-1	1.7933	1.7933	6.6921
4/01/2010	FI0009000053	-1	1.7933	1.7933	6.6921
6/02/2010	FI0009000053	-2	6.6921	1.7933	6.6921
7/02/2010	FI0009000053	-2	6.6921	1.7933	6.6921
17/01/2001	FI0009000145	-1	-7.6491	-7.6491	.
18/01/2001	FI0009000145	-1	-7.6491	-7.6491	.
21/02/2001	FI0009000145	-2	.	-7.6491	.
22/02/2001	FI0009000145	-2	.	-7.6491	.

Can anyone help? Thanks in advance for your help.

Re: Need urgent help!

Astounding — Mon, 10 Sep 2018 11:29:11 GMT

One way:

data want;

do until (last.ISIN);

set have;

by ISIN;

if month=-1 then lag1=OIB;

else if month=-2 then lag2=OIB;

end;

do until (last.ISIN);

set have;

by ISIN;

output;

end;

run;

The top DO loop locates the proper values for LAG1 and LAG2, then the bottom DO loop re-reads the same observations and outputs them.

There might be a way to do this with SQL, but I leave that to the more-SQL-experienced.

Re: Compute 2 lag values

Ksharp — Mon, 10 Sep 2018 13:51:57 GMT

data have;
infile cards expandtabs;
input Date : $20.	ISIN :$20. month	OIB	;
cards;
3/01/2010	FI0009000053	-1	1.7933
4/01/2010	FI0009000053	-1	1.7933
6/02/2010	FI0009000053	-2	6.6921
7/02/2010	FI0009000053	-2	6.6921
17/01/2001	FI0009000145	-1	-7.6491
18/01/2001	FI0009000145	-1	-7.6491
21/02/2001	FI0009000145	-2	.
22/02/2001	FI0009000145	-2	.
;
run;
data want;
 merge have 
 have(keep=ISIN  month	OIB rename=( month=_m	OIB=lag1) where=(_m=-1))
 have(keep=ISIN  month	OIB rename=( month=_m	OIB=lag2) where=(_m=-2)) ;
 by ISIN;
 drop _:;
run;

Re: Compute 2 lag values

ballardw — Mon, 10 Sep 2018 15:24:17 GMT

@bd_user_10 wrote:

Hi Everyone,

I have the following dataset

Date ISIN month OIB

3/01/2010 FI0009000053 -1 1.7933

4/01/2010 FI0009000053 -1 1.7933

6/02/2010 FI0009000053 -2 6.6921

7/02/2010 FI0009000053 -2 6.6921

17/01/2001 FI0009000145 -1 -7.6491

18/01/2001 FI0009000145 -1 -7.6491

21/02/2001 FI0009000145 -2 .

22/02/2001 FI0009000145 -2 .

and I want the following

Date ISIN month OIB lag1 lag2

3/01/2010 FI0009000053 -1 1.7933 1.7933 6.6921

4/01/2010 FI0009000053 -1 1.7933 1.7933 6.6921

6/02/2010 FI0009000053 -2 6.6921 1.7933 6.6921

7/02/2010 FI0009000053 -2 6.6921 1.7933 6.6921

17/01/2001 FI0009000145 -1 -7.6491 -7.6491 .

18/01/2001 FI0009000145 -1 -7.6491 -7.6491 .

21/02/2001 FI0009000145 -2 . -7.6491 .

22/02/2001 FI0009000145 -2 . -7.6491 .

Can anyone help? Thanks in advance for your help.

Maybe my experience is too SAS centric but LAG implies the variable value appearing in records before (above) a given record in a data set. As such I do not understand what you mean by lag1 and lag2 for the first record as there are no preceding values for the first record.

One also sort of has to assume that you mean "per ISIN" but was not stated as a requirement.

Re: Compute 2 lag values

bd_user_10 — Tue, 11 Sep 2018 11:47:11 GMT

These codes works fine!

Re: Compute 2 lag values

bd_user_10 — Tue, 11 Sep 2018 11:50:48 GMT

Hi Ballardw, I should have just said month1 month2 not lag1 and lag2. Silly me!