Desktop productivity for business analysts and programmers

Need help with 'Retain' statement please.

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Need help with 'Retain' statement please.

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;


Accepted Solutions
Solution
‎07-28-2015 02:49 PM
Valued Guide
Posts: 856

Re: Need help with 'Retain' statement please.

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

View solution in original post


All Replies
Solution
‎07-28-2015 02:49 PM
Valued Guide
Posts: 856

Re: Need help with 'Retain' statement please.

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

New Contributor
Posts: 2

Re: Need help with 'Retain' statement please.

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

☑ This topic is SOLVED.

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

Discussion stats
  • 2 replies
  • 266 views
  • 0 likes
  • 2 in conversation