topic Re: Lead2 function similar to Lag2 function in SAS Programming

Lead2 function similar to Lag2 function

gmorla2016 — Tue, 03 May 2016 21:58:17 GMT

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

Re: Lead2 function similar to Lag2 function

FreelanceReinh — Tue, 03 May 2016 22:30:48 GMT

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;

Re: Lead2 function similar to Lag2 function

Reeza — Tue, 03 May 2016 23:49:52 GMT

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

Re: Lead2 function similar to Lag2 function

PGStats — Wed, 04 May 2016 03:05:14 GMT

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;

Re: Lead2 function similar to Lag2 function

gmorla2016 — Wed, 04 May 2016 03:28:27 GMT

Hi,

I have only base sas to run this.

Re: Lead2 function similar to Lag2 function

gmorla2016 — Wed, 04 May 2016 03:30:31 GMT

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;

Re: Lead2 function similar to Lag2 function

Reeza — Wed, 04 May 2016 03:34:14 GMT

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

Re: Lead2 function similar to Lag2 function

gmorla2016 — Wed, 04 May 2016 04:56:24 GMT

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;

Re: Lead2 function similar to Lag2 function

Ksharp — Wed, 04 May 2016 06:56:50 GMT

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;