DATA Step, Macro, Functions and more

Create Status change variable

Accepted Solution Solved
Reply
Contributor
Posts: 44
Accepted Solution

Create Status change variable

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.


Accepted Solutions
Solution
‎09-11-2017 05:04 PM
Super User
Posts: 2,078

Re: Create Status change variable

Posted in reply to WarrenKuhfeld

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


All Replies
SAS Super FREQ
Posts: 508

Re: Create Status change variable

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;

Solution
‎09-11-2017 05:04 PM
Super User
Posts: 2,078

Re: Create Status change variable

Posted in reply to WarrenKuhfeld

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;

Contributor
Posts: 44

Re: Create Status change variable

Posted in reply to novinosrin
Thank you! This works like a charm! Smiley Happy
Contributor
Posts: 44

Re: Create Status change variable

Posted in reply to WarrenKuhfeld

Thanks for the reply.

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

Super User
Posts: 6,939

Re: Create Status change variable

[ Edited ]

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.

Contributor
Posts: 44

Re: Create Status change variable

Posted in reply to Astounding

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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