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

I am a novice SAS user and I am working with this data set and there is a variable called "Status" that keeps track of the current status of the companies right now, either "A" (active) or "I" (inactive). The data is collected from different periods so sometimes the status was "active" for a period but later on it went "inactive". The last Status is always the correct one, so I'd like to know how to change all the Status observations of a company to its last value.

 

I have been squeezing my brain and looking up all over the web and SAS communities. I've tried retain last. first. functions, conditional, loops,... so far, but I can't find the answer.

 

Enclosed is the preview of my results so far. As you can see in the last day of 2013 the company was still active, but at some point from then until now it went inactive and the final status changed right there (red circle). Again, where does the status change in the dataset is not important, if it is "Inactive" anywhere all status should be "inactive", otherwise all status should be "active".

 

Any ideas or directions will be much appreciated. Thank you very much in advance.

 

 sascom.jpg

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

Assuming your data set is in sorted order BY COMPANY DATE, you can do this in one step:

 

data want;

do until (last.company);

   set have;

   by company;

end;

last_status = status;

do until (last.company);

   set have;

   by company;

   output;

end;

run;

 

The top loop reads all the observations for a company, providing the final value for STATUS.  Then the bottom loop reads the same observations and outputs them.

View solution in original post

5 REPLIES 5
Reeza
Super User

That's not clear. Do you want the latest status or that if it's inactive anytime it's inactive always?

 

Please post sample data in text format so we can work with it. 

 

There are many possible solutions, one is a sort and then using FIRST and RETAIN. 

 


@KrisDeng wrote:

I am a novice SAS user and I am working with this data set and there is a variable called "Status" that keeps track of the current status of the companies right now, either "A" (active) or "I" (inactive). The data is collected from different periods so sometimes the status was "active" for a period but later on it went "inactive". The last Status is always the correct one, so I'd like to know how to change all the Status observations of a company to its last value.

 

I have been squeezing my brain and looking up all over the web and SAS communities. I've tried retain last. first. functions, conditional, loops,... so far, but I can't find the answer.

 

Enclosed is the preview of my results so far. As you can see in the last day of 2013 the company was still active, but at some point from then until now it went inactive and the final status changed right there (red circle). Again, where does the status change in the dataset is not important, if it is "Inactive" anywhere all status should be "inactive", otherwise all status should be "active".

 

Any ideas or directions will be much appreciated. Thank you very much in advance.

 

 sascom.jpg


 

KrisDeng
Obsidian | Level 7

Hi.

 

Thank you for your quick reply. I would like to have all the status the same, and similar to the last one. This way readers will see if the company is currently active right now, even if they are looking at an older observation.

 

Attached is the sample of the data for a company fully from it's first to last observation.

 

Thank you very much.

Reeza
Super User

1. Sort by company and descending date -> PROC SORT

2. In a Data Step use BY groups. At the first record (first.VARIABLENAME) of a company it will be the latest status. Create a new variable called LAST_STATUS and set it to the value.  Make sure the variable has a RETAIN

 

retain latest_status;

set have;
by company descending date;

if first.company then latest_status = Status;

If you have trouble with the exact implementation post your full code, log and detail why it isn't working.

 


@KrisDeng wrote:

Hi.

 

Thank you for your quick reply. I would like to have all the status the same, and similar to the last one. This way readers will see if the company is currently active right now, even if they are looking at an older observation.

 

Attached is the sample of the data for a company fully from it's first to last observation.

 

Thank you very much.


 

Astounding
PROC Star

Assuming your data set is in sorted order BY COMPANY DATE, you can do this in one step:

 

data want;

do until (last.company);

   set have;

   by company;

end;

last_status = status;

do until (last.company);

   set have;

   by company;

   output;

end;

run;

 

The top loop reads all the observations for a company, providing the final value for STATUS.  Then the bottom loop reads the same observations and outputs them.

KrisDeng
Obsidian | Level 7

Hi.

 

It worked out beautifully. Thank you very much. I am sure your support will be of great help for me to further studying SAS.

 

Best.

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
  • 5 replies
  • 905 views
  • 0 likes
  • 3 in conversation