Solved
Contributor
Posts: 28

# Creating lead values in sas

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

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
Posts: 5,539

## 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;
year = year - 1;

run;

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

PG

PG

All Replies
Posts: 3,167

## 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_);

run;

proc print;run;

Regards,

Haikuo

Posts: 5,539

## 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

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
Posts: 3,167

## Re: Creating lead values in sas

That is cheating, PG . 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 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;

if i>=2 then do;

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

output;

end;

if last.firm then do;

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

output;

end;

drop i;

run;

proc print;run;

Regards,

Haikuo

Solution
‎07-05-2012 10:42 PM
Posts: 5,539

## 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;
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 and locked.