topic Help with SAS code in SAS Procedures

Help with SAS code

shalmali — Sat, 17 Mar 2012 16:26:32 GMT

Hi,

I have the following. In this data, I want to modify y1 by making y1 = y2
after the year given in y2. In other words, I want the y1 for year 1996, 1997,
and 1998 to be 1996 instead of 1955. Can anyone provide me the code for it? I
am a beginner at SAS and have limited knowledge about SAS.

Year    Firm    y1    y2
1992    AIR    1955    .
1993    AIR    1955    .
1994    AIR    1955    .
1995    AIR    1955    .
1996    AIR    1955    1996
1997    AIR    1955    .
1998    AIR    1955    .
1999    AIR    1996    .
2000    AIR    1996    .
2001    AIR    1996    .
2002    AIR    1996    .
2003    AIR    1996    .

Help with SAS code

Tom — Sat, 17 Mar 2012 17:00:00 GMT

Does the data contain multiple firms? Do they have their own value for Y2? Why is the variable Y2 only populated on one row?

You probably would prefer to have Y2 be the same value for all observations (or all observations for a firm) but you can fix that by creating a new variable that you retain.

data want ;

set have;

retain y2_fix;

if y2 ne . then y2_fix=y2;

if year >= y2_fix then y1=year ;

run;

Help with SAS code

shalmali — Sat, 17 Mar 2012 18:17:44 GMT

Hi Tom,

Thank you for your promt reply. Yes, the data has multiple firms. I created value for Y2 for each firm. So in other words, each firm has Y1 and Y2. In the data given below, I want the value of Y1 before year 1996 to be 1955, for years between 1996 and 2002 to be 1996 and for years after 2003 to be 2003 (assuming value for Y2 after 2003 is missing). Do you think the above program would work in this case?

2004 AIR 1996 .

2005 AIR 2003 .

2006 AIR 2003 .

2007 AIR 2003 .

Thanks once again.

Help with SAS code

PGStats — Sat, 17 Mar 2012 18:27:50 GMT

Well, if you the DATA step route, don't forget to sort your data first. Otherwise you can also do:

proc sql;

create table _subs as select * from have where y2 is not missing;

update have as t

set y1 = coalesce((select y2 from _subs where t.firm=firm and t.y1 = y1 and t.year >= year), y1);

drop table _subs;

select * from have;

Re: Help with SAS code

shalmali — Sat, 17 Mar 2012 19:36:34 GMT

Hi PG,

Thank you for the code. The data is as follows:

2004 AIR 1996 .

2005 AIR 2003 .

2006 AIR 2003 .

2007 AIR 2003 .

1992    BBB 1989    .
1993    BBB 1989    .
1994    BBB   1989    .
1995    BBB    1989    .
1996    BBB   1989
1997    BBB 1989   .
1998    BBB    1989   .
1999    BBB   1999   1999
2000    BBB   1999    .
2001    BBB    1999    .
2002   BBB   1999    .
2003    BBB   1999    .

2004 BBB 1999 2004

2005 BBB 2004 .

2006 BBB 2004 .

2007 BBB 2004 .

I sorted the data by firm and year ..

data want ;

set have;

retain y2_fix;

if y2 ne . then y2_fix=y2;

if year >= y2_fix then y3=year ;

run;

I want to create a new variable Y3. I want the value of Y3 for firm AIR before year 1996 to be 1955, for years between 1996 and 2002 to be 1996 and for years after 2003 to be 2003 (assuming value for Y2 after 2003 is missing). For firm BBB, I want the value of Y3 before 1999 to 1989, between 1999 and 2004 to be 1999, and after 2005 to be 2005.

Help with SAS code

PGStats — Sat, 17 Mar 2012 19:53:57 GMT

Then all that's missing from Tom"s suggestion is the treatment by firm, as in :

data want(drop=_y);
set have;
by firm;
retain _y;
if first.firm then call missing(_y);
if not missing(y2) then _y = y2;
if missing(_y) then y3 = y1; else y3 = _y;
run;

proc print; run;

Help with SAS code

shalmali — Sat, 17 Mar 2012 20:10:05 GMT

Thanks a lot.