Group and create new variable using different inputs per group

[AliRKM]

Hi clever people,

I'm very new to SAS and am struggling to even think of the logic needed to program what I want.
I'm using SAS EG 7.15
My data consists of prescription refills for people purchased over 3 'rounds'. I have already grouped the drugs per round and summed the variables I need.
The rounds are of varying lengths and not all drugs are purchased in all rounds.
I need a sum of days in the 'round' but only if a drug was purchased in the round.

Hard to explain - hopefully this will help:
data WORK.have;
infile datalines dsd truncover;
input ID:32. drug:$12. round:32. r1_days:32. r2_days:32. r3_days:32. drug_days:32. drug_fills:32. pills:32.;
datalines;
10027101 GLYBURIDE 1 60 153 304 90 3 360
10027101 GLYBURIDE 2 60 153 304 150 5 600
10027101 GLYBURIDE 3 60 153 304 120 4 480
10027101 METFORMIN 1 60 153 304 90 3 180
10027101 METFORMIN 2 60 153 304 150 5 300
10027101 METFORMIN 3 60 153 304 360 4 1440
10027101 SAXAGLIPTIN 2 60 153 304 90 1 90
10027101 SAXAGLIPTIN 3 60 153 304 90 1 90
;

data WORK.WANT;
infile datalines dsd truncover;
input id:32. drug:$12. tot_drug_days:32. Tot_drug_fills:32. Tot_pills:32. total_days:32. percent:32.;
datalines;
10027101 GLYBURIDE 360 12 1440 517 0.696324952
10027101 METFORMIN 600 12 1920 517 1.160541586
10027101 SAXAGLIPTIN 180 2 180 457 0.393873085
;

As you can see, SAXAGLIPTIN was purchased in rounds 2 and 3 so I only want to sum r2_days+r3_days whereas for other drugs I need to sum all 3 rounds.

Many thanks.

[ballardw]

Do you need a report (that people read) or a data set?

I would suggest providing an example of the data before you did what ever summary. Your creation of the exact same values of r1_days and such for all "rounds" is going to complicate things a lot because you include values that apparently should be summed.

I think this may be doable without presummarizing but would need to see that data.

It is also a good idea to provide more than work case, such as another ID with a different values. Some solutions that may work for exactly one shown case may not work when applied to different data that has different combinations of present/missing data.

[AliRKM]

Thanks for your reply. I needed a dataset. Thanks for the tips - will include a more diverse data set next time.

[Jagadishkatam]

Please try the below code

data have;
input ID drug$ round  r1_days r2_days r3_days drug_days drug_fills pills;
cards;
1002710 GLYBURIDE 1 60 153 304 90 3 360
1002710 GLYBURIDE 2 60 153 304 150 5 600
1002710 GLYBURIDE 3 60 153 304 120 4 480
1002710 METFORMIN 1 60 153 304 90 3 180
1002710 METFORMIN 2 60 153 304 150 5 300
1002710 METFORMIN 3 60 153 304 360 4 1440
1002710 SAXAGLIPTIN 2 60 153 304 90 1 90
1002710 SAXAGLIPTIN 3 60 153 304 90 1 90
;

data want;
set have;
by id drug;
retain r1_days1 r2_days2 r3_days3;
array vars(*) r1_days r2_days r3_days;
array vars2(*) r1_days1 r2_days2 r3_days3;
do i = 1 to 3;
if first.drug then vars2(i)=.;
if round=i then vars2(i)=vars(i);
end;
total_daysx=sum(r1_days1,r2_days2, r3_days3);
run;

proc sort data=want;
by id drug descending round;
run;

data want2;
do until (last.drug);
set want;
by id drug descending round;
retain tot_drug_days Tot_drug_fills Tot_pills total_days;
if first.drug then tot_drug_days=drug_days;
else tot_drug_days+drug_days;

if first.drug then Tot_drug_fills=drug_fills;
else Tot_drug_fills+drug_fills;

if first.drug then Tot_pills=pills;
else Tot_pills+pills;

if first.drug then total_days=total_daysx;
if last.drug;
end;
do until (last.drug);
set want;
by id drug descending round;
percent=tot_drug_days/total_days;
keep id drug tot_drug_days Tot_drug_fills Tot_pills total_days percent;
end;
run;

[AliRKM]

Thanks Jag, that did not differentiate between drugs only being purchased in different rounds.

[novinosrin]

HI @AliRKM  If I understand your description correctly, it's rather straight forward by group/array summarization.

data have;
input ID drug$ round  r1_days r2_days r3_days drug_days drug_fills pills;
cards;
1002710 GLYBURIDE 1 60 153 304 90 3 360
1002710 GLYBURIDE 2 60 153 304 150 5 600
1002710 GLYBURIDE 3 60 153 304 120 4 480
1002710 METFORMIN 1 60 153 304 90 3 180
1002710 METFORMIN 2 60 153 304 150 5 300
1002710 METFORMIN 3 60 153 304 360 4 1440
1002710 SAXAGLIPTIN 2 60 153 304 90 1 90
1002710 SAXAGLIPTIN 3 60 153 304 90 1 90
;

data want;
 do until(last.drug);
  set have;
  by id drug;
  array r r1_days r2_days r3_days;
  array q drug_days drug_fills pills;
  array t tot_drug_days tot_drug_fills tot_pills;
  do over q;
   t=sum(t,q);
  end;
  tot_days=sum(tot_days,r(round));
 end;
 percent=tot_drug_days/tot_days;
 keep id drug tot: percent; 
run;

proc print noobs;run;

ID	drug	tot_drug_days	tot_drug_fills	tot_pills	tot_days	percent
1002710	GLYBURID	360	12	1440	517	0.69632
1002710	METFORMI	600	12	1920	517	1.16054
1002710	SAXAGLIP	180	2	180	457	0.39387

[AliRKM]

Thanks @novinosrin. That did not work over multiple ID's - as @ballardw mentioned, I should have supplied a dataset with more variety

[AliRKM · Accepted solution]

In the end I found an ugly work-around:

data intermediate;
set have;
if round=1 then days=r1days;
if round=2 then days=r2days;
if round=3 then days=r3days;
run;

proc sort data=intermediate; by id drug round;run;

data want;
set intermediate;
by id, drug;
retain tot_drug_days;
if first.rxdrgnam then tot_drug_days = drug_days;
else tot_drug_days=tot_drug_days+drug-days; /*then the same for fills and pills*/
retain total_days;
if first.rxdrgnam then tot_days = days;
else tot_days=tot_days+days;
percent=tot_drug_days/tot_days;
run;

Thanks for all your help - really need to learn more about arrays...