Conditional value repeating

[Myurathan]

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

[novinosrin · Accepted solution]

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;

[Myurathan]

@novinosrin, Again, Thank you so much for your help.

[Myurathan]

@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.

[novinosrin]

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;

[Myurathan]

@novinosrin It works like charms. Thank you so much.

[cminard]

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;