Multiple 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 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

[ballardw]

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.

[Myurathan]

@ballardw Thank you for your quick reply. I notice that the solution you proposed does not consider the variable Cured.

[novinosrin · Accepted solution]

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;

[Myurathan]

@novinosrin. as usual, you save my day. Thank you so much. I would like to learn data step advanced techniques, and is there any books or document would you recommend me? Thanks a lot.

[novinosrin]

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