Labeling patients based on selection criteria?

[cmccor]

This has been stumping for me.

I have an Rx data set that are observations of medication dispensings. I need to label each patient based on the hierarchical criteria in the flow chart attached.

Example:

I have:

Patient ID	Payment
1	Cash
1	Cash
1	Cash
1	Cash
2	Cash
2	Medicare
2	Medicaid
2	Medicaid
3	Cash
3	Private
3	Cash
3	Private

What I want (based on the attached in the flow chart):

Patient ID	Payment
1	Cash
2	Medicaid
3	Private

[ballardw · Accepted solution]

Your example data is missing at least one of your values, so we can't actual test to see what it looks like.

Because of the structure of your "hierarchy" I would strongly suggest testing for medicaid and medicare first, then medicare of that, Then the private, then voucher and leave cash as the default if none are found.

If there are any other payment possibilities you need to say so.

data have;
input PatientID	Payment $;
datalines;
1	Cash
1	Cash
1	Cash
1	Cash
2	Cash
2	Medicare
2	Medicaid
2	Medicaid
3	Cash
3	Private
3	Cash
3	Private
;
proc sort data=have;
   by patientid;
run;
proc transpose data=have
   out=trans (drop=_name_)
;
by patientid;
var payment;
run;

data want;
	set trans;
	array c col: ;
	if index(catx(',',of c(*)),'Medicaid') then Payment='Medicaid';
	else if index(catx(',',of c(*)),'Medicare') then Payment='Medicare';
	else if index(catx(',',of c(*)),'Private') then Payment='Private';
	else if index(catx(',',of c(*)),'Coupon') then Payment='Coupon';
	else Payment='Cash';
run;

after the logic is working you would add : Drop col: ; to get rid of all the individual values.

Note: if your spelling is not the same EVERY SINGLE TIME for Medicaid, Medicare etc. then you should fix that before starting this. Otherwise your comparisons may get very complicated.

The INDEX function will return a positive value that SAS treats as true for the IF when the word is found. The CATX function combines all of the payments recorded into one long string to search. The array uses the list COL: to group all of the columns by the default variable name that Proc Transpose creates.

If you expect other variables to be included say so now and provide examples.

[cmccor]

This worked. Thank you!

[Ksharp]

data have;
infile cards expandtabs truncover;
input PatientID	Payment $;
datalines;
1	Cash
1	Cash
1	Cash
1	Cash
2	Cash
2	Medicaid
2	Medicaid
2	Medicaid
3	Cash
3	Private
3	Cash
3	Private
;
data want;
if _n_=1 then do;
 if 0 then set have;
 declare hash h();
 h.definekey('Payment');
 h.definedone();
end;
 set have;
 if h.check() ne 0 then output;
 h.ref();
run;

proc print;run;