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.
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.
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.
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;
These codes works fine!
@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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: