topic Re: Need help with 'Retain' statement please. in SAS Enterprise Guide

Need help with 'Retain' statement please.

BHinPHX — Tue, 28 Jul 2015 18:30:20 GMT

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;

Re: Need help with 'Retain' statement please.

Steelers_In_DC — Tue, 28 Jul 2015 18:49:06 GMT

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

Re: Need help with 'Retain' statement please.

BHinPHX — Tue, 28 Jul 2015 19:11:21 GMT

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