BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
AmirSari
Quartz | Level 8

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.

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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;

View solution in original post

6 REPLIES 6
WarrenKuhfeld
Rhodochrosite | Level 12

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;

novinosrin
Tourmaline | Level 20

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;

AmirSari
Quartz | Level 8
Thank you! This works like a charm! 🙂
AmirSari
Quartz | Level 8

Thanks for the reply.

The last google record has a status of missing because there is no change in status from 2002 to 2003.

Astounding
PROC Star

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.

AmirSari
Quartz | Level 8

Thank you for your reply. unfortunately the code you provided produces missing vlaues for the variable status across all observations.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1167 views
  • 4 likes
  • 4 in conversation