DATA Step, Macro, Functions and more

Lead2 function similar to Lag2 function

Reply
Occasional Contributor
Posts: 7

Lead2 function similar to Lag2 function

[ Edited ]

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.

 

  
TNAMENoPeriodLead1Lead2
IM200Oct-15201202
IM201Nov-15202203
IM202Dec-15203204
IM203Jan-16204205
IM204Feb-16205206
IM205Mar-16206207
IM206Apr-16207208
IM207May-16208209
IM208Jun-16209210
IM209Jul-16210211
IM210Aug-16211212
IM211Sep-16212.
IM212Oct-16..
TE100Oct-15101102
TE101Nov-15102103
TE102Dec-15103104
TE103Jan-16104105
TE104Feb-16105106
TE105Mar-16106107
TE106Apr-16107108
TE107May-16108109
TE108Jun-16109110
TE109Jul-16110111
TE110Aug-16111112
TE111Sep-16112.
TE112Oct-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

Trusted Advisor
Posts: 1,118

Re: Lead2 function similar to Lag2 function

Posted in reply to gmorla2016

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;
Occasional Contributor
Posts: 7

Re: Lead2 function similar to Lag2 function

Posted in reply to FreelanceReinhard

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;

Super User
Posts: 19,865

Re: Lead2 function similar to Lag2 function

Posted in reply to gmorla2016

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

Super User
Posts: 19,865

Re: Lead2 function similar to Lag2 function

Posted in reply to gmorla2016

If you have SAS/ETS you can use PROC EXPAND to create your lag/lead variables.

Occasional Contributor
Posts: 7

Re: Lead2 function similar to Lag2 function

Hi,

 

I have only base sas to run this.

 

 

Respected Advisor
Posts: 4,932

Re: Lead2 function similar to Lag2 function

Posted in reply to gmorla2016

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;
PG
Occasional Contributor
Posts: 7

Re: Lead2 function similar to Lag2 function

Thanks a lot  

 

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;

 

Super User
Posts: 10,044

Re: Lead2 function similar to Lag2 function

Posted in reply to gmorla2016

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;
Ask a Question
Discussion stats
  • 8 replies
  • 354 views
  • 0 likes
  • 5 in conversation