BookmarkSubscribeRSS Feed
ash3
Calcite | Level 5

I want to fill missing observation in status variable on 2 condition

1. if bucket =4 then status= "bad"

2. if previous bucket ="bad" and current bucket <4 then status = "recover"

 

so based on these two condition all missing obs should be "recover" but could not apply this logic on sas. Please help

 

id  bucket status

1    0   good

1    4    bad

1    0    ""

1    0   ""

1    1    ""

1    2   ""

1    3   ""

1    4    bad

 

2 REPLIES 2
PeterClemmensen
Tourmaline | Level 20

2 points:

 

  1. Your desired result indicate that the logic should not be 'if previous bucket ..' . It should be 'if any of the previous bucket values within the same id group equals 4.
  2. I take it that the logic should hold for different by-groups. 

 

Try the code below.

 

data have;
input id bucket;
datalines;
1 0 
1 4 
1 0 
1 0 
1 1 
1 2 
1 3 
1 4 
2 0 
2 4 
2 0 
2 0 
2 1 
2 2 
2 3 
2 4 
;

data want(drop = prev_4);
   set have;
   by id;

   if first.id then prev_4 = 0;
   if bucket = 4 then prev_4 = 1;

   length status $20;

   status = ifc(bucket = 4, 'bad', 'good');

   if prev_4 = 1 & bucket < 4 then status = 'recover';

   retain prev_4;
run;

 

Result:

 

id  bucket  status
1   0       good
1   4       bad
1   0       recover
1   0       recover
1   1       recover
1   2       recover
1   3       recover
1   4       bad
2   0       good
2   4       bad
2   0       recover
2   0       recover
2   1       recover
2   2       recover
2   3       recover
2   4       bad

 

andreas_lds
Jade | Level 19

What should happen in this case?

id  bucket status
42    0   good
42    0   ?
42    0   ?
42    1   ?
42    2   ?
42    3   ?
42    4    bad

Or is such a case not possible?

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 688 views
  • 1 like
  • 3 in conversation