DATA Step, Macro, Functions and more

How do I update all observations of a variable (9.4)

Accepted Solution Solved
Reply
Contributor
Posts: 22
Accepted Solution

How do I update all observations of a variable (9.4)

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


Accepted Solutions
Solution
‎11-14-2017 08:31 PM
Super User
Posts: 6,637

Re: How do I update all observations of a variable (9.4)

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


All Replies
Super User
Posts: 23,323

Re: How do I update all observations of a variable (9.4)

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


 

Contributor
Posts: 22

Re: How do I update all observations of a variable (9.4)

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.

Super User
Posts: 23,323

Re: How do I update all observations of a variable (9.4)

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.


 

Solution
‎11-14-2017 08:31 PM
Super User
Posts: 6,637

Re: How do I update all observations of a variable (9.4)

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.

Contributor
Posts: 22

Re: How do I update all observations of a variable (9.4)

Posted in reply to Astounding

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.

☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 153 views
  • 0 likes
  • 3 in conversation