topic How do I update all observations of a variable (9.4) in SAS Programming

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

KrisDeng — Tue, 14 Nov 2017 23:53:24 GMT

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.

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

Reeza — Wed, 15 Nov 2017 00:15:06 GMT

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.

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

KrisDeng — Wed, 15 Nov 2017 00:21:58 GMT

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.

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

Reeza — Wed, 15 Nov 2017 00:36:12 GMT

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.

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

Astounding — Wed, 15 Nov 2017 00:52:34 GMT

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.

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

KrisDeng — Wed, 15 Nov 2017 01:32:13 GMT

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.