topic Re: Flag Subsequent Records based on initial 'Anchor' record in SAS Programming

Flag Subsequent Records based on initial 'Anchor' record

shounster — Wed, 15 Nov 2017 22:10:52 GMT

Will probably be easier to see that to explain in the subject line, so thank you for looking.

Attempting to flag a PAC (post acute care) record based on whether or not there was an acute stay prior to the PAC visit.

data have;

input key $ prov_type $ key_lag $ readm_days;

datalines;

971 acute 0 0

971 ltac 971 0

971 irf 971 0

510 psych 971 0

510 acute 0 0

510 HH 510 1

;

run;

What I would like to see is a new flag on row 2 and 6. I get repeating flags on 6,7,8. Row 4 should not have a flag because it occurs before (chronologically) the next acute record.

This is the code I've been banging up against the wall for awhile. It also might make more sense than my 'english' from above.

data all_claims4;
set all_claims3;
by key;

if key=key_lg then do;
if first.key
and prov_type ne 'ACUTE'
and readm_days le 5 then first_pac='Y';
end;

run;

I would be glad to provide more detail if necessary. There are other variables in the set like admission date that I am not using for this run, but if it makes sense to include that variable for the solution, I can.

Thank you in advance for sharing your wisdom.

-Brian

Re: Flag Subsequent Records based on initial 'Anchor' record

ballardw — Wed, 15 Nov 2017 23:23:25 GMT

See if this is doing what you want without the days considered.

data have;
   input key $ prov_type $ ;
datalines;
971 acute 
971 ltac 
971 irf 
510 psych 
510 acute 
510 HH 
510 HH 
510 HH 
;
run;
/* assume the data in Have is in the order you want to process*/
data want;
   set have;
   by notsorted key;
   Retain Flag 0;
   lp=lag(prov_type);
   if first.key then flag=0;
   else flag= (lp = 'acute');
   drop lp;
run;

Since you didn't show any values for the readm_days larger than 1 this should work for your example data.

To add day limit

else flag= (lp = 'acute' and readm_days <5);

BTW there are many good reasons to code yes/no as 1/0 instead of character. For instance the sum within a group of records is number of yes values, the mean is the percentage. Also SAS will treat 1 as 'true' (actually that's how the value is assigned in this code) so you can use statements like:

If flag then ...

instead of having to explicitly us

If flag='Y' then ...

And if a person wants to read Yes or No use a custom format to display desired text for reports.

Re: Flag Subsequent Records based on initial 'Anchor' record

Astounding — Thu, 16 Nov 2017 00:00:29 GMT

I think I have the right intent here, but it may depend on what should happen if the same KEY has multiple ACUTE observations.

data all_claims4;
set all_claims3;
by key;
if first.key then flag=0;
if upcase(prov_type) = 'ACUTE' and flag=0 then flag = 1;

if upcase(prov_type) ne 'ACUTE' and readm_days le 5 and flag=1 then do;

first_pac='Y';

output;

first_pac=' ';

flag=5;

end;

else output;

retain flag;

drop flag;
run;

Re: Flag Subsequent Records based on initial 'Anchor' record

shounster — Thu, 16 Nov 2017 13:24:53 GMT

Thank you! I just kept getting stuck on the flagging of the variables I needed. Also, thank you for the tip on the Y/N v 1/0!

Re: Flag Subsequent Records based on initial 'Anchor' record

shounster — Thu, 16 Nov 2017 13:25:25 GMT

Thank you so very much. This solution worked as well. I appreciate you taking the time.