Compute 2 lag values

[bd_user_10]

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.

[Astounding · Accepted solution]

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.

[Ksharp]

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;

[bd_user_10]

These codes works fine!

[ballardw]

---

@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.

[bd_user_10]

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