topic Re: retain date when first instance of default in SAS Procedures

retain date when first instance of default

Steelers_In_DC — Thu, 22 Jan 2015 21:37:41 GMT

I am trying to identify when a record first goes into default and retain that date as a default date. If the record comes out of default and then goes back into default that would reset the default date. I cannot figure out how to make this happen. Below are have and want datasets with my progress up to this point.

Thank You,

data data;

infile cards dsd dlm=',';

informat record_date mmddyy10.;

format record_date mmddyy10.;

input power_id $ record_date default_reason $;

cards;

test1,8/31/2013,

test1,9/30/2013,PD

test1,10/31/2013,PD

test1,11/30/2013,PD

test1,12/31/2013,

test1,1/31/2014,

test1,2/28/2014,

test1,3/31/2014,PD

test1,4/30/2014,PD

test1,5/31/2014,

test1,6/30/2014,

test1,7/31/2014,

test1,8/31/2014,

test1,9/30/2014,

test1,10/31/2014,

test1,11/30/2014,

test1,12/31/2014,

test1,1/31/2015,

test2,2/28/2015,PD

test2,3/31/2015,PD

test2,4/30/2015,

test2,5/31/2015,

test2,6/30/2015,

test2,7/31/2015,

test2,8/31/2015,

test2,9/30/2015,

test2,10/31/2015,

test2,11/30/2015,

test2,12/31/2015,PD

test2,1/31/2016,PD

test2,2/29/2016,

test2,3/31/2016,

test2,4/30/2016,

test2,5/31/2016,

test2,6/30/2016,

;

run;

data have(keep=power_id record_date default_reason pass_date pass_count first_default_flag);

format pass_date mmddyy10.;

retain pass_date first_default_flag;

set data;

by power_id;

pass_count + 1;

if first.power_id or default_reason ne '' then pass_date = .;

if first.power_id then pass_count = 0;

/*account for records that do not start in default*/

if first.power_id and default_reason = '' then first_default_flag = 0;

else if default_reason ne '' then first_default_flag = 1;

if first_default_flag = 1 then do;

if first.power_id then pass_date = .;

if first.power_id then pass_count = 0;

if default_reason ne '' then pass_count = 0;

if pass_count = 7 then pass_date = record_date;

if pass_count > 6 and default_reason ne '' then pass_date = .;

end;

run;

data want;

infile cards dsd;

informat record_date pass_date default_date mmddyy10.;

format record_date pass_date default_date mmddyy10.;

input power_id $ record_date default_reason $ pass_date default_date first_default_flag pass_count;

cards;

test1,08/31/2013,,,,0,0

test1,09/30/2013,PD,,09/30/2013,1,0

test1,10/31/2013,PD,,09/30/2013,1,0

test1,11/30/2013,PD,,09/30/2013,1,0

test1,12/31/2013,,,09/30/2013,1,1

test1,01/31/2014,,,09/30/2013,1,2

test1,02/28/2014,,,09/30/2013,1,3

test1,03/31/2014,PD,,09/30/2013,1,0

test1,04/30/2014,PD,,09/30/2013,1,0

test1,05/31/2014,,,09/30/2013,1,1

test1,06/30/2014,,,09/30/2013,1,2

test1,07/31/2014,,,09/30/2013,1,3

test1,08/31/2014,,,09/30/2013,1,4

test1,09/30/2014,,,09/30/2013,1,5

test1,10/31/2014,,,09/30/2013,1,6

test1,11/30/2014,,11/30/2014,09/30/2013,1,7

test1,12/31/2014,,11/30/2014,09/30/2013,1,8

test1,01/31/2015,,11/30/2014,09/30/2013,1,9

test2,02/28/2015,PD,,02/28/2015,1,0

test2,03/31/2015,PD,,02/28/2015,1,0

test2,04/30/2015,,,02/28/2015,1,1

test2,05/31/2015,,,02/28/2015,1,2

test2,06/30/2015,,,02/28/2015,1,3

test2,07/31/2015,,,02/28/2015,1,4

test2,08/31/2015,,,02/28/2015,1,5

test2,09/30/2015,,,02/28/2015,1,6

test2,10/31/2015,,10/31/2015,02/28/2015,1,7

test2,11/30/2015,,10/31/2015,02/28/2015,1,8

test2,12/31/2015,PD,,12/31/2015,1,0

test2,01/31/2016,PD,,12/31/2015,1,0

test2,02/29/2016,,,12/31/2015,1,1

test2,03/31/2016,,,12/31/2015,1,2

test2,04/30/2016,,,12/31/2015,1,3

test2,05/31/2016,,,12/31/2015,1,4

test2,06/30/2016,,,12/31/2015,1,5

;

run;

Re: retain date when first instance of default

morgalr — Fri, 23 Jan 2015 01:03:53 GMT

This is not a trivial exercise, I developed the exact solution you ask for as a solution for a Federal requirement for a project called "Case Mix" when I worked at Washington States DSHS Aging and Disability Services. You can request the code from them, it is public disclosable, but I no longer work for them. Their code is in VBA and MS Access, but it should be easily convertible to SAS.

Our requirement was to track residents in and out of nursing facilities and apply assessments to their periods they were in the facility according to Federal guidelines. There were time lines that had to be applied and defaults if they did not do an assessment in the time given as a grace period by the Federal Government. If the resident left the facility before the "Grace Period" was over, then when they came in they had a new grace period start and any resulting rate, or default, had to be retroactively applied to all open grace periods.

Fun times and took almost a year to work all the bugs out, but it still stands as the "go to way to do it" after more than a decade.

Re: retain date when first instance of default

Steelers_In_DC — Fri, 23 Jan 2015 01:37:14 GMT

Below is a solution. Anyone is welcome to poke holes in it or recommend a more creative way to come up with the same solution. This works but seems remedial to me:

data data;

infile cards dsd;

informat record_date mmddyy10.;

format record_date mmddyy10.;

input power_id $ record_date default_reason $;

cards;

test1,8/31/2013,

test1,9/30/2013,PD

test1,10/31/2013,

test1,11/30/2013,

test1,12/31/2013,

test1,1/31/2014,

test1,2/28/2014,

test1,3/31/2014,

test1,4/30/2014,PD

test1,5/31/2014,

test1,6/30/2014,

test1,7/31/2014,

test1,8/31/2014,

test1,9/30/2014,pd

test1,10/31/2014,pd

test1,11/30/2014,

test1,12/31/2014,

test1,1/31/2015,

test1,2/28/2015,

test1,3/31/2015,

test1,4/30/2015,

test1,5/31/2015,

test1,6/30/2015,

test1,7/31/2015,

test1,8/31/2015,pd

test1,9/30/2015,

test1,10/31/2015,

test1,11/30/2015,

test1,12/31/2015,

test1,1/31/2016,

test1,2/29/2016,

test1,3/31/2016,

test2,8/31/2013,PD

test2,9/30/2013,PD

test2,10/31/2013,

test2,11/30/2013,

test2,12/31/2013,

test2,1/31/2014,

test2,2/28/2014,PD

test2,3/31/2014,

test2,4/30/2014,

test2,5/31/2014,

test2,6/30/2014,

test2,7/31/2014,

test2,8/31/2014,

test2,9/30/2014,

test2,10/31/2014,pd

test2,11/30/2014,

test2,12/31/2014,

test2,1/31/2015,

test2,2/28/2015,pd

test2,3/31/2015,pd

test2,4/30/2015,

test2,5/31/2015,

test2,6/30/2015,

test2,7/31/2015,

test2,8/31/2015,

test2,9/30/2015,

test2,10/31/2015,

test2,11/30/2015,

test2,12/31/2015,

test2,1/31/2016,pd

test2,2/29/2016,

;

run;

data have(keep=power_id record_date default_reason pass_date pass_count first_default_flag lpass_count);

format pass_date mmddyy10.;

retain pass_date first_default_flag;

set data;

by power_id;

pass_count + 1;

/*if first.power_id or default_reason ne '' then pass_date = .;*/

if first.power_id then pass_count = 0;

/*account for records that do not start in default*/

if first.power_id and default_reason = '' then first_default_flag = 0;

else if default_reason ne '' then first_default_flag = 1;

if first_default_flag = 1 then do;

if first.power_id then pass_date = .;

if first.power_id then pass_count = 0;

if default_reason ne '' then pass_count = 0;

/* if pass_count = 0 then pass_date = .;*/

end;

lpass_count = lag(pass_count);

if lpass_count = 6 then pass_date = record_date;

run;

data want;

format default_date mmddyy10.;

retain default_date;

set have;

by power_id;

pidcount +1;

default_count +1;

if default_reason = '' then default_count = 0;

if first.power_id then pidcount = 1;

if first.power_id and not missing(default_reason) then default_date = record_date;

if not first.power_id then do;

if default_count = 1 and lpass_count > 5 then default_date = record_date;

end;

if default_count = 1 and pass_count = 0 and lpass_count = 0 and not missing(default_reason) then default_date = record_date;

run;

proc sql;

create table default_date as

select

power_id,

record_date,

default_reason,

default_date,

pass_date

from want

order by

power_id,

record_date;

Re: retain date when first instance of default

Haikuo — Fri, 23 Jan 2015 02:33:05 GMT

The following seems less verbose :

data data;

infile cards dsd dlm=',';

informat record_date mmddyy10.;

format record_date mmddyy10.;

input power_id $ record_date default_reason $;

cards;

test1,8/31/2013,

test1,9/30/2013,PD

test1,10/31/2013,PD

test1,11/30/2013,PD

test1,12/31/2013,

test1,1/31/2014,

test1,2/28/2014,

test1,3/31/2014,PD

test1,4/30/2014,PD

test1,5/31/2014,

test1,6/30/2014,

test1,7/31/2014,

test1,8/31/2014,

test1,9/30/2014,

test1,10/31/2014,

test1,11/30/2014,

test1,12/31/2014,

test1,1/31/2015,

test2,2/28/2015,PD

test2,3/31/2015,PD

test2,4/30/2015,

test2,5/31/2015,

test2,6/30/2015,

test2,7/31/2015,

test2,8/31/2015,

test2,9/30/2015,

test2,10/31/2015,

test2,11/30/2015,

test2,12/31/2015,PD

test2,1/31/2016,PD

test2,2/29/2016,

test2,3/31/2016,

test2,4/30/2016,

test2,5/31/2016,

test2,6/30/2016,

;

run;

data want;

set data;

by power_id default_reason notsorted;

retain default_date first_default_flag pass_date;

format default_date pass_date mmddyy10.;

if first.power_id then

do;

default_date=.;

first_default_flag =0;

end;

if first.default_reason then

pass_count=0;

pass_count+1;

if default_reason='PD' then

do;

default_date=coalesce(default_date, record_date);

first_default_flag=1;

pass_count=0;

end;

if first.power_id then

pass_count=0;

if pass_count >6 then

pass_date=coalesce(pass_date, record_date);

else pass_date=.;

run;

Re: retain date when first instance of default

Steelers_In_DC — Fri, 23 Jan 2015 03:06:14 GMT

Hai.kuo,

After 6 months of no default events the pass date is generated. If there is another default event at or after that time the default date is reset and retained from that date unless there is another pass date and default event following. If you look at line 9 and 25 of our results side by side you will see examples of what I am talking about.

Re: retain date when first instance of default

Haikuo — Fri, 23 Jan 2015 04:16:11 GMT

Hmmm. My previous attempt was to generate the identical 'want' in your first post, I wasn't pay attention to your down stream development as I assume there should be no change unless you flag it out. So here it is, less verbose.

data data;

infile cards dsd;

informat record_date mmddyy10.;

format record_date mmddyy10.;

input power_id $ record_date default_reason $;

cards;

test1,8/31/2013,

test1,9/30/2013,PD

test1,10/31/2013,

test1,11/30/2013,

test1,12/31/2013,

test1,1/31/2014,

test1,2/28/2014,

test1,3/31/2014,

test1,4/30/2014,PD

test1,5/31/2014,

test1,6/30/2014,

test1,7/31/2014,

test1,8/31/2014,

test1,9/30/2014,pd

test1,10/31/2014,pd

test1,11/30/2014,

test1,12/31/2014,

test1,1/31/2015,

test1,2/28/2015,

test1,3/31/2015,

test1,4/30/2015,

test1,5/31/2015,

test1,6/30/2015,

test1,7/31/2015,

test1,8/31/2015,pd

test1,9/30/2015,

test1,10/31/2015,

test1,11/30/2015,

test1,12/31/2015,

test1,1/31/2016,

test1,2/29/2016,

test1,3/31/2016,

test2,8/31/2013,PD

test2,9/30/2013,PD

test2,10/31/2013,

test2,11/30/2013,

test2,12/31/2013,

test2,1/31/2014,

test2,2/28/2014,PD

test2,3/31/2014,

test2,4/30/2014,

test2,5/31/2014,

test2,6/30/2014,

test2,7/31/2014,

test2,8/31/2014,

test2,9/30/2014,

test2,10/31/2014,pd

test2,11/30/2014,

test2,12/31/2014,

test2,1/31/2015,

test2,2/28/2015,pd

test2,3/31/2015,pd

test2,4/30/2015,

test2,5/31/2015,

test2,6/30/2015,

test2,7/31/2015,

test2,8/31/2015,

test2,9/30/2015,

test2,10/31/2015,

test2,11/30/2015,

test2,12/31/2015,

test2,1/31/2016,pd

test2,2/29/2016,

;

run;

data want_1;

set data;

by power_id default_reason notsorted;

retain default_date pass_date _six_ct;

format default_date pass_date mmddyy10.;

if first.power_id then call missing( default_date, pass_date, _six_ct);

if first.default_reason then _pass_ct=0;

if missing(default_reason) then _pass_ct+1;

if lag(_pass_ct)=6 then do;pass_date=record_date;_six_ct=1;end;

if not missing(default_reason) and (_six_ct=1 or missing(default_date)) then default_date=record_date;

if not missing(default_reason) then _six_ct=.;

drop _:;

run;

Re: retain date when first instance of default

Steelers_In_DC — Fri, 23 Jan 2015 05:05:19 GMT

Very nice solution. Thank you very much.

Re: retain date when first instance of default

Ksharp — Fri, 23 Jan 2015 05:53:36 GMT

So if pass_count gt 6 you need to change pass_date default_date ?

data data;
infile cards dsd dlm=',';
informat record_date mmddyy10.;
format record_date mmddyy10.;
input power_id $ record_date default_reason $;
cards;
test1,8/31/2013,
test1,9/30/2013,PD
test1,10/31/2013,PD
test1,11/30/2013,PD
test1,12/31/2013,
test1,1/31/2014,
test1,2/28/2014,
test1,3/31/2014,PD
test1,4/30/2014,PD
test1,5/31/2014,
test1,6/30/2014,
test1,7/31/2014,
test1,8/31/2014,
test1,9/30/2014,
test1,10/31/2014,
test1,11/30/2014,
test1,12/31/2014,
test1,1/31/2015,
test2,2/28/2015,PD
test2,3/31/2015,PD
test2,4/30/2015,
test2,5/31/2015,
test2,6/30/2015,
test2,7/31/2015,
test2,8/31/2015,
test2,9/30/2015,
test2,10/31/2015,
test2,11/30/2015,
test2,12/31/2015,PD
test2,1/31/2016,PD
test2,2/29/2016,
test2,3/31/2016,
test2,4/30/2016,
test2,5/31/2016,
test2,6/30/2016,
;
run;   
data temp; 
 set data;
 by power_id;
 if first.power_id or (default_reason='PD' and lag(default_reason)=' ') then group+1;
run;
data want;
 set temp;
 by power_id group;
 retain pass_date default_date first_default_flag pass_count  ;
 if first.group then do;
   if default_reason='PD' and (first.power_id or pass_count gt 6 or pass_count=0) then default_date=record_date;
   first_default_flag=0; pass_date=.;pass_count=0;
 end;
 if default_reason='PD' then first_default_flag=1;
 if default_reason=' ' and first_default_flag=1 then pass_count+1;
 if pass_count eq 7 then pass_date=record_date;
 format   pass_date default_date mmddyy10.;
 drop group;
 run;

Xia Keshan

Re: retain date when first instance of default

Ksharp — Fri, 23 Jan 2015 06:17:00 GMT

Bian,

Forgive me to pick you up . I have lots of time , so take a look at your code . :smileysilly:

data data;

infile cards dsd truncover;

informat record_date mmddyy12.;

format record_date mmddyy10.;

input power_id $ record_date default_reason $;

cards;

test1,2/21/2013,

test1,3/21/2013,

test1,4/21/2013,

test1,5/21/2013,

test1,6/21/2013,

test1,7/21/2013,

test1,8/21/2013,

test1,9/20/2013,PD

test1,10/31/2013,

test1,11/30/2013,

test1,12/31/2013,

test1,1/31/2014,

test1,2/28/2014,

test1,3/31/2014,

;

run;

will get wrong result .

pass_date should be missing.