topic Re: Conditional value repeating in SAS Programming

Conditional value repeating

Myurathan — Fri, 10 Jul 2020 17:08:31 GMT

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

Re: Conditional value repeating

novinosrin — Fri, 10 Jul 2020 17:46:01 GMT


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;

Re: Conditional value repeating

cminard — Fri, 10 Jul 2020 18:00:17 GMT

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;

Re: Conditional value repeating

Myurathan — Mon, 13 Jul 2020 14:50:38 GMT

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

Re: Conditional value repeating

Myurathan — Wed, 05 Aug 2020 13:53:46 GMT

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

Re: Conditional value repeating

novinosrin — Wed, 05 Aug 2020 14:09:07 GMT

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;

Re: Conditional value repeating

Myurathan — Wed, 05 Aug 2020 14:17:26 GMT

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