BookmarkSubscribeRSS Feed
NewSASPerson
Quartz | Level 8

I need some help and I hope I am able to explain this correctly. I would like to calculate the days a particular drug is missed per year by month.                                          

For the examples that I have, The patient filled the drug early for some months and I would like to shift the actual dates so there are no negative missed days.                                        

                                               

So for drug A, the total missed days should be 73 and for Drug B the total missed days should be 147                                        

But  I am stumped on how to calculate this without the negative values.                                

                                               

Any assistance would be appreciated                                     

 

 

ID First Name Last Name Medication Fill Date Days Supply Next Refill Date Days In Beween
1234 John Doe Drug A 1/23/2021 90    
1234 John Doe Drug A 4/1/2021 30 4/23/2021 -22
1234 John Doe Drug A 7/30/2021 30 5/1/2021 90
1234 John Doe Drug A 8/23/2021 30 8/29/2021 -6
1234 John Doe Drug A 9/23/2021 30 9/22/2021 1
1234 John Doe Drug A 10/19/2021 30 10/23/2021 -4
1234 John Doe Drug A 11/11/2021 30 11/18/2021 -7
1234 John Doe Drug B 1/2/2021 90    
1234 John Doe Drug B 8/27/2021 30 4/2/2021 147
1234 John Doe Drug B 9/23/2021 30 9/26/2021 -3
1234 John Doe Drug B 10/19/2021 30 10/23/2021 -4
1234 John Doe Drug B 11/11/2021 30 11/18/2021 -7
1234 John Doe Drug B 12/22/2021 30 12/11/2021 11
2 REPLIES 2
Kurt_Bremser
Super User

See this:

data have;
infile datalines dlm="09"x dsd truncover;
input
  ID $
  First_Name $
  Last_Name $
  Medication $
  Fill_Date :mmddyy10.
  Days_Supply
  Next_Refill_Date :mmddyy10.
  Days_In_Between
;
format Fill_Date Next_Refill_Date yymmdd10.;
datalines;
1234	John	Doe	Drug A	1/23/2021	90	 	 
1234	John	Doe	Drug A	4/1/2021	30	4/23/2021	-22
1234	John	Doe	Drug A	7/30/2021	30	5/1/2021	90
1234	John	Doe	Drug A	8/23/2021	30	8/29/2021	-6
1234	John	Doe	Drug A	9/23/2021	30	9/22/2021	1
1234	John	Doe	Drug A	10/19/2021	30	10/23/2021	-4
1234	John	Doe	Drug A	11/11/2021	30	11/18/2021	-7
1234	John	Doe	Drug B	1/2/2021	90	 	 
1234	John	Doe	Drug B	8/27/2021	30	4/2/2021	147
1234	John	Doe	Drug B	9/23/2021	30	9/26/2021	-3
1234	John	Doe	Drug B	10/19/2021	30	10/23/2021	-4
1234	John	Doe	Drug B	11/11/2021	30	11/18/2021	-7
1234	John	Doe	Drug B	12/22/2021	30	12/11/2021	11
;

data want;
set have;
by id medication;
retain first_fill total_supply;
format first_fill last_date yymmdd10.;
if first.medication
then do;
  first_fill = fill_date;
  total_supply = days_supply;
end;
else total_supply + days_supply;
if last.medication
then do;
  last_date = fill_date + days_supply;
  missed_days = last_date - first_fill - total_supply;
  output;
end;
drop fill_date days_supply next_refill_date days_in_between;
run;

It sums the total supply, calculates the total time span covered, and builds a sum and difference from that.

The result differs from what you wanted, so your logic may be different.

 

Notre how I presented example data in usable form that leaves no question about variable attributes and real content. Please do so in the future.

NewSASPerson
Quartz | Level 8
Thank you. However, I need to be able to split the days missed by months.

So for instance for Drug A, I have -22 days missed in April. I can not have negative days.

I need to change the fill dates so that I end up with positive days missed for each month.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 2 replies
  • 296 views
  • 0 likes
  • 2 in conversation