- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Tags:
- SAS programming
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;