BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
BHinPHX
Calcite | Level 5

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Steelers_In_DC
Barite | Level 11

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

2 REPLIES 2
Steelers_In_DC
Barite | Level 11

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
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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