Creating lead values in sas

Accepted Solution Solved
Reply
Contributor
Posts: 28
Accepted Solution

Creating lead values in sas

Can anyone please help me in calculating the lead value of a given variable? For instance I have the following data set.

Firm     Year      SIZE

A          2001     10

A          2002     20

A          2003     13

A          2004     11

B          2001     19

B          2002     14

B          2003     16

B          2004     12

C          2001     17

C          2002     15

C          2003     18

C          2004     13

I want to create a new variable Lead_SIZE which will be the lead value of the SIZE

Firm     Year      SIZE     Lead_size

A          2001     10          20

A          2002     20          13

A          2003     13          11

A          2004     11          .

B          2001     19          14

B          2002     14          16

B          2003     16          12

B          2004     12          .

C          2001     17          15

C          2002     15          18

C          2003     18          13

C          2004     13          .

Thank you.


Accepted Solutions
Solution
‎07-05-2012 10:42 PM
Respected Advisor
Posts: 4,640

Re: Creating lead values in sas

So many choices, so many ways (still using corrupted data) ...

data have;
input Firm $ Year SIZE;
datalines;
A          2001     10
A          2002     20
A          2003     13
A          2004     11
B          2001     19
B          2002     14
B          2003     16
B          2004     12
C          2001     17
C          2002     15
C          2004     18
C          2005     13
;

proc sort data=have; by firm year; run;

data up(keep=firm year lead_size) / view=up;
set have;
lead_size = size;
year = year - 1;

run;

data want;
merge have(in=ok) up;
by firm year;
if ok;
run;

PG

PG

View solution in original post


All Replies
Respected Advisor
Posts: 3,124

Re: Creating lead values in sas

There will be many solutions, such as proc expand etc. , while the following is the classic 'look ahead':

data have;

input Firm  $   Year      SIZE;

cards;

A          2001     10

A          2002     20

A          2003     13

A          2004     11

B          2001     19

B          2002     14

B          2003     16

B          2004     12

C          2001     17

C          2002     15

C          2003     18

C          2004     13

;

data want;

set have; by firm;

set have (firstobs=2 keep=size rename=size=lead_size) have(obs=1 drop=_all_);

lead_size=ifn(last.firm,.,lead_size);

run;

proc print;run;

Regards,

Haikuo

Respected Advisor
Posts: 4,640

Re: Creating lead values in sas

Indeed, there are many ways. If speed is not a primary concern and you prefer simpler to understand coding or if you want lead_size to reflect the size of the next year and not of the next record (notice the absence of a size for C in 2003), or if you don't want to worry about sorting, then this will do better, imho :

data have;

input Firm $ Year SIZE;

cards;

A          2001     10

A          2002     20

A          2003     13

A          2004     11

B          2001     19

B          2002     14

B          2003     16

B          2004     12

C          2001     17

C          2002     15

C          2004     18

C          2005     13

;

proc sql;

create table want as

select a.*, b.size as lead_Size

from have as a left join have as b on a.firm=b.firm and a.year=b.year-1;

select * from want;

quit;

PG

PG
Respected Advisor
Posts: 3,124

Re: Creating lead values in sas

That is cheating, PG Smiley Wink. Very smart though.

Here is another array() approach learned long time ago from Ksharp:

data have;

input Firm  $   Year      SIZE;

cards;

A          2001     10

A          2002     20

A          2003     13

A          2004     11

B          2001     19

B          2002     14

B          2003     16

B          2004     12

C          2001     17

C          2002     15

C          2003     18

C          2004     13

;

data want;

array lead(0:1,1:3) _temporary_;

array ls (0:1)$ _temporary_;

set have;

by firm;

if first.firm then call missing(i,of lead(*), of ls(*));

i+1;

ls(mod(i,2))=firm;

lead(mod(i,2),2)=year ;

lead(mod(i,2),3)=size;

if i>=2 then do;

    firm=ls(mod(i+1,2));

    year=lead(mod(i+1,2),2);

    size=lead(mod(i+1,2),3);

    lead_size=lead(mod(i,2),3);

    output;

end;

if last.firm then do;

    firm=ls(mod(i,2));

    year=lead(mod(i,2),2);

    size=lead(mod(i,2),3);

    call missing(lead_size);

    output;

end;

drop i;

run;

proc print;run;

Regards,

Haikuo

Solution
‎07-05-2012 10:42 PM
Respected Advisor
Posts: 4,640

Re: Creating lead values in sas

So many choices, so many ways (still using corrupted data) ...

data have;
input Firm $ Year SIZE;
datalines;
A          2001     10
A          2002     20
A          2003     13
A          2004     11
B          2001     19
B          2002     14
B          2003     16
B          2004     12
C          2001     17
C          2002     15
C          2004     18
C          2005     13
;

proc sort data=have; by firm year; run;

data up(keep=firm year lead_size) / view=up;
set have;
lead_size = size;
year = year - 1;

run;

data want;
merge have(in=ok) up;
by firm year;
if ok;
run;

PG

PG
Contributor
Posts: 28

Re: Creating lead values in sas

Thank you all for the codes.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 680 views
  • 9 likes
  • 3 in conversation