Hi,
I need to calculate the Lead1 Lead2 similar to Lag1 and Lag2 .Here is the input and desired output. TNAME, No and Period are input fields and Lead1 and Lead2 are desired output.
TNAME | No | Period | Lead1 | Lead2 |
IM | 200 | Oct-15 | 201 | 202 |
IM | 201 | Nov-15 | 202 | 203 |
IM | 202 | Dec-15 | 203 | 204 |
IM | 203 | Jan-16 | 204 | 205 |
IM | 204 | Feb-16 | 205 | 206 |
IM | 205 | Mar-16 | 206 | 207 |
IM | 206 | Apr-16 | 207 | 208 |
IM | 207 | May-16 | 208 | 209 |
IM | 208 | Jun-16 | 209 | 210 |
IM | 209 | Jul-16 | 210 | 211 |
IM | 210 | Aug-16 | 211 | 212 |
IM | 211 | Sep-16 | 212 | . |
IM | 212 | Oct-16 | . | . |
TE | 100 | Oct-15 | 101 | 102 |
TE | 101 | Nov-15 | 102 | 103 |
TE | 102 | Dec-15 | 103 | 104 |
TE | 103 | Jan-16 | 104 | 105 |
TE | 104 | Feb-16 | 105 | 106 |
TE | 105 | Mar-16 | 106 | 107 |
TE | 106 | Apr-16 | 107 | 108 |
TE | 107 | May-16 | 108 | 109 |
TE | 108 | Jun-16 | 109 | 110 |
TE | 109 | Jul-16 | 110 | 111 |
TE | 110 | Aug-16 | 111 | 112 |
TE | 111 | Sep-16 | 112 | . |
TE | 112 | Oct-16 | . | . |
DATA TEST;
INPUT TNAME $1-2 NO 3-5 Period $6-11;
CARDS;
IM200Oct-15
IM201Nov-15
IM202Dec-15
IM203Jan-16
IM204Feb-16
IM205Mar-16
IM206Apr-16
IM207May-16
IM208Jun-16
IM209Jul-16
IM210Aug-16
IM211Sep-16
IM212Oct-16
TE100Oct-15
TE101Nov-15
TE102Dec-15
TE103Jan-16
TE104Feb-16
TE105Mar-16
TE106Apr-16
TE107May-16
TE108Jun-16
TE109Jul-16
TE110Aug-16
TE111Sep-16
TE112Oct-16
;
RUN;
Thanks
Ganesh
Hi Ganesh,
One solution is this:
data want;
merge test
test(firstobs=2 keep=tname no rename=(tname=tname1 no=Lead1))
test(firstobs=3 keep=tname no rename=(tname=tname2 no=Lead2));
if tname1 ne tname then lead1=.;
if tname2 ne tname then lead2=.;
drop tname1 tname2;
run;
Please note that MERGE is used without a BY statement here, so you may want to set (for this step)
options mergenoby=nowarn;
hi,
thanks for looking into this but i have more one variable infact many variables and i need this to be more generic for a by group combination. please see below data. i need a more generic solution.
DATA TEST;
INPUT FNAME $1-3 LNAME $4-6 NO 7-9 Period $10-15;
CARDS;
KIMJIM200Oct-15
KIMJIM201Nov-15
KIMJIM202Dec-15
KIMJIM203Jan-16
KIMJIM204Feb-16
KIMJIM205Mar-16
KIMJIM206Apr-16
KIMJIM207May-16
KIMJIM208Jun-16
KIMJIM209Jul-16
KIMJIM210Aug-16
KIMJIM211Sep-16
KIMJIM212Oct-16
TIMJOE100Oct-15
TIMJOE101Nov-15
TIMJOE102Dec-15
TIMJOE103Jan-16
TIMJOE104Feb-16
TIMJOE105Mar-16
TIMJOE106Apr-16
TIMJOE107May-16
TIMJOE108Jun-16
TIMJOE109Jul-16
TIMJOE110Aug-16
TIMJOE111Sep-16
TIMJOE112Oct-16
;
RUN;
@FreelanceReinh Solution is quite generic and will work, you can't get around the renaming aspect, any method will require renaming.
If you have many variables you can dynamically build the rename statements by querying the sashelp.vcolumn table to generate a macro variable with the names. Do your names have any structure to them? Or do the variables you want to lag have any system?
Another option would be to use IML - the SAS matrix language which is more flexible for things like lead/lag.
If you have SAS/ETS you can use PROC EXPAND to create your lag/lead variables.
Hi,
I have only base sas to run this.
Another solution is :
proc sort data=test; by tname descending no; run;
data want;
do until(last.tname);
set test; by tname;
output;
lead2 = lead1;
lead1 = no;
end;
run;
proc sort data=want; by tname no; run;
Thanks a lot PGStats
Here is the solution i built based on your solution.
DATA TEST;
INPUT FNAME $1-3 LNAME $4-6 NO 7-9 Period $10-15 rank 16-17;
CARDS;
KIMJIM208Oct-1501
KIMJIM201Nov-1502
KIMJIM209Dec-1503
KIMJIM207Jan-1604
KIMJIM214Feb-1605
KIMJIM225Mar-1606
KIMJIM206Apr-1607
KIMJIM227May-1608
KIMJIM298Jun-1609
KIMJIM219Jul-1610
KIMJIM110Aug-1611
KIMJIM311Sep-1612
KIMJIM213Oct-1613
TIMJOE101Oct-1501
TIMJOE121Nov-1502
TIMJOE112Dec-1503
TIMJOE333Jan-1604
TIMJOE124Feb-1605
TIMJOE185Mar-1606
TIMJOE176Apr-1607
TIMJOE777May-1608
TIMJOE198Jun-1609
TIMJOE119Jul-1610
TIMJOE111Aug-1611
TIMJOE222Sep-1612
TIMJOE112Oct-1613
;
RUN;
%let bygroup = Fname LNAME;
%let lastvar = %scan(&bygroup,-1);
proc sort data=test; by &bygroup. descending rank descending no; run;
data want;
do until(last.&lastvar);
set test; by &bygroup.;
output;
lead2 = lead1;
lead1 = no;
end;
run;
proc sort data=want; by &bygroup. rank; run;
data final_table;
set want;
array z {*} no lead1 lead2;
Min_of_next3months = min(of z[*]);
run;
Use the most right group variable if your dataset has been sorted by group variables.
DATA TEST;
INPUT FNAME $1-3 LNAME $4-6 NO 7-9 Period $10-15 rank 16-17;
CARDS;
KIMJIM208Oct-1501
KIMJIM201Nov-1502
KIMJIM209Dec-1503
KIMJIM207Jan-1604
KIMJIM214Feb-1605
KIMJIM225Mar-1606
KIMJIM206Apr-1607
KIMJIM227May-1608
KIMJIM298Jun-1609
KIMJIM219Jul-1610
KIMJIM110Aug-1611
KIMJIM311Sep-1612
KIMJIM213Oct-1613
TIMJOE101Oct-1501
TIMJOE121Nov-1502
TIMJOE112Dec-1503
TIMJOE333Jan-1604
TIMJOE124Feb-1605
TIMJOE185Mar-1606
TIMJOE176Apr-1607
TIMJOE777May-1608
TIMJOE198Jun-1609
TIMJOE119Jul-1610
TIMJOE111Aug-1611
TIMJOE222Sep-1612
TIMJOE112Oct-1613
;
RUN;
data want;
merge test
test(firstobs=2 keep=LNAME no rename=(LNAME =tname1 no=Lead1))
test(firstobs=3 keep=LNAME no rename=(LNAME =tname2 no=Lead2));
if tname1 ne LNAME then lead1=.;
if tname2 ne LNAME then lead2=.;
drop tname1 tname2;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.