## Lead2 function similar to Lag2 function

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.

 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

Posts: 1,256

## Re: Lead2 function similar to Lag2 function

Hi Ganesh,

One solution is this:

``````data want;
merge test
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

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: 23,770

## Re: Lead2 function similar to Lag2 function

@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: 23,770

## Re: Lead2 function similar to Lag2 function

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.

Posts: 5,536

## Re: Lead2 function similar to Lag2 function

Another solution is :

``````proc sort data=test; by tname descending no; run;

data want;
do until(last.tname);
set test; by tname;
output;
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;

end;

run;

proc sort data=want; by &bygroup. rank; run;

data final_table;

set want;

Min_of_next3months = min(of z[*]);

run;

Super User
Posts: 10,784

## Re: Lead2 function similar to Lag2 function

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;``````
Discussion stats
• 8 replies
• 386 views
• 0 likes
• 5 in conversation