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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
AliRKM
Obsidian | Level 7
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...

View solution in original post

7 REPLIES 7
ballardw
Super User

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
Obsidian | Level 7
Thanks for your reply. I needed a dataset. Thanks for the tips - will include a more diverse data set next time.
Jagadishkatam
Amethyst | Level 16

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;
Thanks,
Jag
AliRKM
Obsidian | Level 7

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

novinosrin
Tourmaline | Level 20

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
Obsidian | Level 7
Thanks @novinosrin. That did not work over multiple ID's - as @ballardw mentioned, I should have supplied a dataset with more variety
AliRKM
Obsidian | Level 7
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...

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 7 replies
  • 1264 views
  • 0 likes
  • 4 in conversation