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
ID | Date | Status | Cured |
A | 01/01/2000 | No def | |
A | 02/01/2000 | Default | |
A | 03/01/2000 | Default | |
A | 04/01/2000 | No def | |
A | 05/01/2000 | No def | |
A | 06/01/2000 | No def | |
A | 07/01/2000 | No def | Cured |
A | 08/01/2000 | No def | Cured |
A | 09/01/2000 | No def | Cured |
A | 10/01/2000 | Default | |
A | 11/01/2000 | Default | |
B | 01/01/2000 | Default | |
B | 02/01/2000 | Default | |
B | 03/01/2000 | No def | |
B | 04/01/2000 | No def | |
B | 05/01/2000 | Default | |
B | 06/01/2000 | Default | |
B | 07/01/2000 | No def | |
B | 08/01/2000 | No def | |
B | 09/01/2000 | No def | |
B | 10/01/2000 | No def | Cured |
B | 11/01/2000 | Default | |
B | 12/01/2000 | Default | |
B | 13/01/2000 | No Def |
Want_table
ID | Date | Status | Cured | Default_Date |
A | 01/01/2000 | No def | . | |
A | 02/01/2000 | Default | 02/01/2000 | |
A | 03/01/2000 | Default | 02/01/2000 | |
A | 04/01/2000 | No def | 02/01/2000 | |
A | 05/01/2000 | No def | 02/01/2000 | |
A | 06/01/2000 | No def | 02/01/2000 | |
A | 07/01/2000 | No def | Cured | 02/01/2000 |
A | 08/01/2000 | No def | Cured | 02/01/2000 |
A | 09/01/2000 | No def | Cured | 02/01/2000 |
A | 10/01/2000 | Default | 10/01/2000 | |
A | 11/01/2000 | Default | 10/01/2000 | |
B | 01/01/2000 | Default | 01/01/2000 | |
B | 02/01/2000 | Default | 01/01/2000 | |
B | 03/01/2000 | No def | 01/01/2000 | |
B | 04/01/2000 | No def | 01/01/2000 | |
B | 05/01/2000 | Default | 01/01/2000 | |
B | 06/01/2000 | Default | 01/01/2000 | |
B | 07/01/2000 | No def | 01/01/2000 | |
B | 08/01/2000 | No def | 01/01/2000 | |
B | 09/01/2000 | No def | 01/01/2000 | |
B | 10/01/2000 | No def | Cured | 01/01/2000 |
B | 11/01/2000 | Default | 11/01/2000 | |
B | 12/01/2000 | Default | 11/01/2000 | |
B | 13/01/2000 | No Def | 11/01/2000 |
Thank you so much for your help and time.
Regards,
Myu
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;
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.
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;
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
By Paul Dorfman and Don Henderson
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.