I have an EG data set with three fields: CUST, EVENT, and STATUS. EVENT is a chronologic counter of events by CUST and STATUS is an assigned field based on some if-then-else. What I want to do is assign a value of "BAD" to all subsequent blank STATUS fields - but only where the last STATUS value = 'BAD'. I'm using a RETAIN statement (code is below). The problem is this logic captures the last 'not missing' value and copies it down so in the example below, customer 789 would have a STATUS value of "AVERAGE" for event 3 and 4.
Is there a way to add additional criteria so I only use a 'not missing' value of "BAD"? I've tried several things but none work.
Many thanks!
Current results:
CUST EVENT STATUS
123456 1 GOOD
123456 2 BAD
123456 3
123456 4
789 1 GOOD
789 2 AVERAGE
789 3
789 4
Desired results:
CUST EVENT STATUS
123456 1 GOOD
123456 2 BAD
123456 3 BAD
123456 4 BAD
789 1 GOOD
789 2 AVERAGE
789 3
789 4
DATA VALIDATION;
SET VALIDATION;
RETAIN _STATUS;
BY CUST;
IF NOT MISSING(STATUS) THEN _STATUS=STATUS;
ELSE STATUS=_STATUS;
DROP _STATUS;
RUN;
Here is one way to do it:
data have;
infile cards dsd;
input CUST$ EVENT$ STATUS$;
cards;
123456,1,GOOD
123456,2,BAD
123456,3,
123456,4,
789,1,GOOD
789,2,AVERAGE
789,3,
789,4,
;
data want;
set have;
retain _status;
if not missing(status) then _status = status;
if _status = 'BAD' then status = _status;
drop _:;
run;
Note: The first time I went through this I did the same exact thing you did, same thought process. If you don't drop the unwanted variable it will give you a better visual of what is happening and help you work through it.
Message was edited by: Mark Johnson
Here is one way to do it:
data have;
infile cards dsd;
input CUST$ EVENT$ STATUS$;
cards;
123456,1,GOOD
123456,2,BAD
123456,3,
123456,4,
789,1,GOOD
789,2,AVERAGE
789,3,
789,4,
;
data want;
set have;
retain _status;
if not missing(status) then _status = status;
if _status = 'BAD' then status = _status;
drop _:;
run;
Note: The first time I went through this I did the same exact thing you did, same thought process. If you don't drop the unwanted variable it will give you a better visual of what is happening and help you work through it.
Message was edited by: Mark Johnson
Works perfectly. Thanks Mark, you're the best.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.