Need help with 'Retain' statement please.

[BHinPHX]

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;

[Steelers_In_DC · Accepted solution]

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

[BHinPHX]

Works perfectly.  Thanks Mark, you're the best.