DATA Step, Macro, Functions and more

Summarize overlapping medications

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 128
Accepted Solution

Summarize overlapping medications

[ Edited ]

Hi All, I have the following dataset 

id rx
1  a
1  b
1  d
2  a 
2  b
3  a
3 c
4  b
5 a
5 d
6 a
6 c
7 a
7 b

I am trying to summarize these combination as aggregate, here is the output I want  

a-b-d 1

a-b  2

a-c  2

b    1

a-d  1

 


Accepted Solutions
Solution
‎10-12-2016 02:57 PM
Super User
Posts: 11,343

Re: Summarize overlapping medications

Posted in reply to lillymaginta

For data in that form this works:

data have;
   input patient_id rx $;
datalines;
1              a
1              b
1              c
2              a
2              b
3              a
3              b
;
run;

proc sort data=have; 
  by patient_id rx;
run;

proc transpose data=have
   out=trans;
by patient_id;
var rx;
run;

data want;
   set trans;
   array c col: ;
   length rx_list $ 200;
   rx_list= catx('-',of c(*));
   drop col: _name_ ;
run;

But this will only work if there is one RX per record and the RX doesn't repeat, unless you want a-a-b. If you don't want the repeats then add NODUPKEY to the Proc sort.

 

 

The sort also is used to keep from getting a-b and b-a as different results.

 

I am concerned about the patient 4 in your original post though. If you have multiple RX in a single record then you should consider making sure the structure is one RX per patient record which will involve some additional code.

View solution in original post


All Replies
Super User
Posts: 11,343

Re: Summarize overlapping medications

Posted in reply to lillymaginta

How do you get "a-c 2" and "a-d 1" from that data? You seem to be missing a detail or two on what you are counting and how to count it. At least I think those are supposed to be counts.

How many values may be in RX for a single row?

Frequent Contributor
Posts: 128

Re: Summarize overlapping medications

For some reason, when I edit the dataset i get it correct but it is not when posted. Here are the correct entries:

id rx
1  a
1  b
1  d
2  a 
2  b
3  a
3 c
4  b
5 a
5 d
6 a
6 c
7 a
7 b

 for patient 4 (b), patient 5 (a, d), patient 6 (a, c) and patients 7 (a,b) so you get 2 for a-c because patient 3 had a-c and patient 6 has a-c too. You are right for a-d, the triple combination can be counted under different category so for patient 1, it could be a-b, a-d and b-d and a-b-d but for smipliciy, I would count this only once as a-b-c so patient can be counted only one time in the aggregate measure. 

Super User
Posts: 11,343

Re: Summarize overlapping medications

Posted in reply to lillymaginta

Patient? Your example data does not cleary identify "Patient". If the ID is Patient then the example data is incomplete for the summary result shown.

Frequent Contributor
Posts: 128

Re: Summarize overlapping medications

Apologize if I am not clear, I will simplify my example. 

patient_id rx
1              a
1              b
1              c
2              a
2              b
3              a
3              b

 The output would be

a-b-c     1

a-b        2

Solution
‎10-12-2016 02:57 PM
Super User
Posts: 11,343

Re: Summarize overlapping medications

Posted in reply to lillymaginta

For data in that form this works:

data have;
   input patient_id rx $;
datalines;
1              a
1              b
1              c
2              a
2              b
3              a
3              b
;
run;

proc sort data=have; 
  by patient_id rx;
run;

proc transpose data=have
   out=trans;
by patient_id;
var rx;
run;

data want;
   set trans;
   array c col: ;
   length rx_list $ 200;
   rx_list= catx('-',of c(*));
   drop col: _name_ ;
run;

But this will only work if there is one RX per record and the RX doesn't repeat, unless you want a-a-b. If you don't want the repeats then add NODUPKEY to the Proc sort.

 

 

The sort also is used to keep from getting a-b and b-a as different results.

 

I am concerned about the patient 4 in your original post though. If you have multiple RX in a single record then you should consider making sure the structure is one RX per patient record which will involve some additional code.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 409 views
  • 1 like
  • 2 in conversation