BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Steelers_In_DC
Barite | Level 11

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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;

View solution in original post

8 REPLIES 8
morgalr
Obsidian | Level 7

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.

Steelers_In_DC
Barite | Level 11

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;

Haikuo
Onyx | Level 15

The following seems less verbose Smiley Happy:

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;

Steelers_In_DC
Barite | Level 11

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.

Haikuo
Onyx | Level 15

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;

Steelers_In_DC
Barite | Level 11

Very nice solution.  Thank you very much. 

Ksharp
Super User

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.

Ksharp
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1050 views
  • 0 likes
  • 4 in conversation