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

Deal Experts. 

 

I have the following quest. 

 

Default_date variable should be updated with the first instance of "Default" for the Status variable, and it has to be kept the same date until Status changes to "Default" again and there should be "Cured" for variable Cured once. If there are no "Cured" flag in between these dates, first "Default" instance will be kept as the Default_date. 

 

I hope following example can explain better. 

Have_table

IDDateStatusCured
A01/01/2000No def 
A02/01/2000Default 
A03/01/2000Default 
A04/01/2000No def 
A05/01/2000No def 
A06/01/2000No def 
A07/01/2000No defCured
A08/01/2000No defCured
A09/01/2000No defCured
A10/01/2000Default 
A11/01/2000Default 
B01/01/2000Default 
B02/01/2000Default 
B03/01/2000No def 
B04/01/2000No def 
B05/01/2000Default 
B06/01/2000Default 
B07/01/2000No def 
B08/01/2000No def 
B09/01/2000No def 
B10/01/2000No defCured
B11/01/2000Default 
B12/01/2000Default 
B13/01/2000No Def 

Want_table

IDDateStatusCuredDefault_Date
A01/01/2000No def .
A02/01/2000Default 02/01/2000
A03/01/2000Default 02/01/2000
A04/01/2000No def 02/01/2000
A05/01/2000No def 02/01/2000
A06/01/2000No def 02/01/2000
A07/01/2000No defCured02/01/2000
A08/01/2000No defCured02/01/2000
A09/01/2000No defCured02/01/2000
A10/01/2000Default 10/01/2000
A11/01/2000Default 10/01/2000
B01/01/2000Default 01/01/2000
B02/01/2000Default 01/01/2000
B03/01/2000No def 01/01/2000
B04/01/2000No def 01/01/2000
B05/01/2000Default 01/01/2000
B06/01/2000Default 01/01/2000
B07/01/2000No def 01/01/2000
B08/01/2000No def 01/01/2000
B09/01/2000No def 01/01/2000
B10/01/2000No defCured01/01/2000
B11/01/2000Default 11/01/2000
B12/01/2000Default 11/01/2000
B13/01/2000No Def 11/01/2000

 

Thank you so much for your help and time.

 

Regards,

Myu

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20
data want;
 set have;
 by id status notsorted;
 retain Default_Date;
 if first.id then call missing(Default_Date);
 if first.status and missing(Default_Date) and  status='Default' then Default_Date=date;
 if lag(cured)='Cured' and status='Default' then Default_Date=date;
run;

View solution in original post

5 REPLIES 5
ballardw
Super User

Something like this should work:

data want;
   set have;
   by id;
   retain default_date; 
   if first.id then call missing(default_date);
   if status='Default' then default_date=date;
   format default_date mmddyy10.;
run;

This will assume the data is sorted by ID. If not sorted but all the same Id values are together add NOTSORTED to the BY statement. The By statement will create automatic variables first. and last. that allow you tell whether the current record is first, last or neither for a group from the by statement. The values are numeric 1/0 for true and false.

That allows resetting the Retained variable to missing for the first record of an ID.

Retain is the instruction that keeps the value of a variable across records. This assumes that your DATE is a SAS date value, numeric and likely format of MMDDYY10 from the shown example. If your Date variable is actually character then you would 1) add a LENGTH statement before the Retain to set the length of the default_date variable and 2) remove the format statement.

 

Myurathan
Quartz | Level 8
@ballardw Thank you for your quick reply. I notice that the solution you proposed does not consider the variable Cured.
novinosrin
Tourmaline | Level 20
data want;
 set have;
 by id status notsorted;
 retain Default_Date;
 if first.id then call missing(Default_Date);
 if first.status and missing(Default_Date) and  status='Default' then Default_Date=date;
 if lag(cured)='Cured' and status='Default' then Default_Date=date;
run;
Myurathan
Quartz | Level 8
@novinosrin. as usual, you save my day. Thank you so much. I would like to learn data step advanced techniques, and is there any books or document would you recommend me? Thanks a lot.
novinosrin
Tourmaline | Level 20

1. Learning SAS® by Example: A Programmer's Guide, Second Edition

By Ron Cody

 

2. Cody's Collection of Popular SAS® Programming Tasks and How to Tackle Them

By Ron Cody

 

3. Practical and Efficient SAS®Programming: The Insider's Guide 
By Martha Messineo

 

4. Data Management Solutions Using SAS® Hash Table Operations: A Business Intelligence Case Study 

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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
  • 5 replies
  • 1018 views
  • 3 likes
  • 3 in conversation