Deal Experts.
I have the following quest.
Default_date variable should be updated with the first instance of "Default" for the Status variable, and thereafter it has to have the same date until It changes back to "no def" for the Status variable. I think the following example can explain better.
Have_table
ID | Date | Status |
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 | Default |
A | 06/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 | No Default |
Want_table
ID | Date | Status | 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 | . |
A | 05/01/2000 | Default | 05/01/2000 |
A | 06/01/2000 | Default | 05/01/2000 |
B | 01/01/2000 | Default | 01/01/2000 |
B | 02/01/2000 | Default | 01/01/2000 |
B | 03/01/2000 | No def | . |
B | 04/01/2000 | No Def | . |
B | 05/01/2000 | Default | 05/01/2000 |
B | 06/01/2000 | No Default | . |
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 or status ne 'Default' then Default_Date=.;
if first.status and status='Default' then Default_Date=date;
run;
data want;
set have;
by id status notsorted;
retain Default_Date;
if first.id or status ne 'Default' then Default_Date=.;
if first.status and status='Default' then Default_Date=date;
run;
@novinosrin If I want to have the defaut_date as follows, how should I change the code?
ID | Date | Status | 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 | Default | 05/01/2000 |
A | 06/01/2000 | Default | 05/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 | 05/01/2000 |
B | 06/01/2000 | No Def | 05/01/2000 |
Defult_date variable will have the date when is the last date status moved from No Def to Default.
Thanks in advance.
Hi @Myurathan , Please try the below and let me know-
data have;
input ID $ Date :mmddyy10. Status & $;* Default_Date;
cards;
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 Default 05/01/2000
A 06/01/2000 Default 05/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 05/01/2000
B 06/01/2000 No Def 05/01/2000
;
data want;
set have;
by id Status notsorted;
retain Default_Date;
if first.id then Default_Date=.;
if first.status and status='Default' then Default_Date=date;
format Default_Date mmddyy10.;
run;
data one;
input
@1 id $2.
@3 date mmddyy10.
@14 status $10.
;
format date mmddyy10.;
datalines;
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 Default
A 06/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 No Default
;
run;
proc print data=one;
run;
data two;
set one;
by id date;
format default_date mmddyy10.;
retain default_date;
if first.id then do;
if status in ('No def','No Default') then default_date=.;
if status='Default' then default_date=date;
end;
else do;
if status in ('No def','No Default') then default_date=.;
if status='Default' then default_date=date;
end;
run;
proc print date=two;
run;
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: