Help with SAS code

[shalmali]

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    .

[Tom]

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;

[shalmali]

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?

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    2003

2004    AIR    1996    .

2005    AIR    2003    .

2006    AIR    2003    .

2007    AIR    2003    .

Thanks once again.

[PGStats]

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;

PG

[shalmali]

Hi PG,

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

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    2003

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.

[PGStats]

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;

PG

[shalmali]

Thanks a  lot.