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

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 IDPayment
1Cash
1Cash
1Cash
1Cash
2Cash
2Medicare
2Medicaid
2Medicaid
3Cash
3Private
3Cash
3Private

 

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

Patient IDPayment
1Cash
2Medicaid
3Private
1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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.

View solution in original post

3 REPLIES 3
ballardw
Super User

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
Fluorite | Level 6
This worked. Thank you!
Ksharp
Super User
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;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 3 replies
  • 417 views
  • 0 likes
  • 3 in conversation