Hi all,
I am having a hard time creating a variable that indicates the changes in the status of firm on a list. For example, in the following subsample, Apple was not on the list for years 2000 and 2002, but it is selected to the list in year 2002, so the variable status will take the value of 1 in 2002 and will be missing in 2000, 2001, and 2003. In 2004, however, Apple is dropped from the list, i.e. another change in the status of Apple, so the variables status will take the value of zero.
Firm name year id list status
Apple 2000 21 0 .
Apple 2001 21 0 .
Apple 2002 21 1 1
Apple 2003 21 1 .
Apple 2004 21 0 0
Google 2000 43 1 .
Google 2001 43 1 .
Google 2002 43 0 0
Google 2003 43 0 .
Any help on how to create this Status variable would be greatly appreciated.
data have;
input Firm_name $ year id list status;
drop status;
datalines;
Apple 2000 21 0 .
Apple 2001 21 0 .
Apple 2002 21 1 1
Apple 2003 21 1 .
Apple 2004 21 0 0
Google 2000 43 1 .
Google 2001 43 1 .
Google 2002 43 0 0
Google 2003 43 0 .
;
data want;
set have;
by firm_name list notsorted;
retain status;
if first.firm_name then status=.;
if not first.firm_name and lag(list) ne list and list=1 then status=1;
else if not first.firm_name and lag(list) ne list and list=0 then status=0;
else status=.;
run;
Why does the last google record have a status of missing instead of 0? Assuming it is supposed to be zero, this will do it. If not, something like this will do it. Make sure it is doing what you want if there is one record (missing or 0?).
data x;
input FirmName $ year id list desiredstatus;
cards;
Apple 2000 21 0 .
Apple 2001 21 0 .
Apple 2002 21 1 1
Apple 2003 21 1 .
Apple 2004 21 0 0
Google 2000 43 1 .
Google 2001 43 1 .
Google 2002 43 0 0
Google 2003 43 0 .
;
data x2;
set x; by notsorted id;
status = ifn(list eq lag(list), ., 1);
if last.id then status = 0;
if first.id then status = .;
run;
proc print; run;
data have;
input Firm_name $ year id list status;
drop status;
datalines;
Apple 2000 21 0 .
Apple 2001 21 0 .
Apple 2002 21 1 1
Apple 2003 21 1 .
Apple 2004 21 0 0
Google 2000 43 1 .
Google 2001 43 1 .
Google 2002 43 0 0
Google 2003 43 0 .
;
data want;
set have;
by firm_name list notsorted;
retain status;
if first.firm_name then status=.;
if not first.firm_name and lag(list) ne list and list=1 then status=1;
else if not first.firm_name and lag(list) ne list and list=0 then status=0;
else status=.;
run;
Thanks for the reply.
The last google record has a status of missing because there is no change in status from 2002 to 2003.
If I understand correctly, you have LIST and need to use it to create STATUS. (If I'm wrong about that, don't bother to read the rest.) Here is one way:
data want;
set have;
by FirmName list notsorted;
if first.FirmName then status=.;
else if first.list then status=list;
run;
****** EDITED:
Now you made me go back and test the code .... but this program produced exactly what you asked for. If you got something different, you'll have to show the log from what you ran.
Thank you for your reply. unfortunately the code you provided produces missing vlaues for the variable status across all observations.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.