DATA Step, Macro, Functions and more

Calculations based on person, drug, AND date

Accepted Solution Solved
Reply
Contributor
Posts: 45
Accepted Solution

Calculations based on person, drug, AND date

Hello everyone,


I am having difficulty trying to do some calculations based on member ID, drug_ID, and drug fill_date. Please take a look at the table below. Everything in black are variables and values I currently have in my dataset. Everything in green are variables and values I want to calculate but do not know how to do it.

 

For every ID member, they can have one drug filled on a date or multiple drugs filled on a date. Depending whether the multiple drugs are the same drug or different drugs changes the calculations.

 

Guidelines for calculating "Dispense_Event" are:

1) For one drug with an amount of 30 units or less on one date, it is considered one dispense event. If the one drug has more than 30 units, then divide by 30 and round the final numerical answer down to the nearest whole integer. 

2) For multiple claims for the same drug on the same date, then the drug quantities are added then divided by 30. The final numerical figure is then rounded down to the nearest whole integer.

3) For multiple drugs filled on the same date, then the calculations should adhere to the previous two guidelines (refer to patient ID "3" for reference). 

 

IDDrug_IDRX_FILL_DTRx_AmtAggregate_Rx_Amt_per_DTDispense_EventAggreg._Disp.Events per ID
171/1/2016303013
181/1/201615151
181/2/201615301
181/2/201615
281/1/2016153011
281/1/201615
371/1/2016306024
371/1/201630
371/2/201630301
381/3/201615301
381/3/201615 ... ......
471/3/2016303011
581/2/2016153011
581/2/201615
671/1/2016306023
671/1/201630
681/1/201615151

 

In short, I need to add up all the drug quantities per drug per date for each ID member, then use the drug total unit per memeber per date to calculate the dispense_events, and then finally add up all the dispense events for each ID. 

 

I wanted to use "RETAIN" but got bugged down by the logistics of the calculations. 

 

I am using SAS 9.4 and would greatly appreciate your help! 

 

Muchas Gracias


Accepted Solutions
Solution
‎10-15-2016 01:47 PM
Super User
Posts: 10,028

Re: Calculations based on person, drug, AND date

Posted in reply to TXSASneophyte

Maybe ROUND() is what you are looking for .

 

 

data have;
input ID  Drug_ID RX_FILL_DT : mmddyy10. Rx_Amt	;
format rx_fill_dt mmddyy10.;
cards;
1   7   1/1/2016    30
1   8   1/1/2016    15
1   8   1/2/2016    15
1   8   1/2/2016    15
2   8   1/1/2016    15
2   8   1/1/2016    15
3   7   1/1/2016    30
3   7   1/1/2016    30
3   7   1/2/2016    30
3   8   1/3/2016    15
3   8   1/3/2016    15
4   7   1/3/2016    30
5   8   1/2/2016    15
5   8   1/2/2016    15
6   7   1/1/2016    30
6   7   1/1/2016    30
6   8   1/1/2016    15
;
run;
proc sql;
create table want as
select *,round(sum(rx_amt)/30) as Aggreg_DispEventsperID
from 
(
 select *,sum(rx_amt) as sum_rx_amt,
  round(calculated sum_rx_amt/30) as dispense_event 
  from have
   group by id,drug_id,rx_fill_dt
) 
group by id
   ;
quit;

View solution in original post


All Replies
Super User
Posts: 11,343

Re: Calculations based on person, drug, AND date

Posted in reply to TXSASneophyte

First question do you NEED a data set as a result or a report?

And if you need a data set does it need to have every record from the input or is a summary with the identification information and the summary values sufficient?

Contributor
Posts: 45

Re: Calculations based on person, drug, AND date

If possible, I do need a dataset with every record from the input because there are additional steps I'll be doing after this. This is another one of the reasons why I backed away from the "RETAIN" function. This is the beginning of a longer code I'll be doing and every record will be needed. 

Super User
Posts: 11,343

Re: Calculations based on person, drug, AND date

Posted in reply to TXSASneophyte

Are you using ... to indicate missing or something else? A single dot is standard for SAS users. If there is another value that should appear here then please be explicit.

 

Also is it critical that the summary value appear with the first drug date combination?

Contributor
Posts: 45

Re: Calculations based on person, drug, AND date

My apologies, I should have explained the ellipses (...). They do not have any particular meaning actually, it was my attempt to simplify the guidelines for how to calculate the "dispense_events" variable (I thought the ellipses would make it easier for people to understand my request). For the dataset, what I envision is something like "proc sql group by function".  Here is the table without the ellipses. 

 

IDDrug_IDRX_FILL_DTRx_AmtAggregate_Rx_Amt_per_DTDispense_EventAggreg._Disp.Events per ID
171/1/2016303013
181/1/2016151513
181/2/2016153013
181/2/2016153013
281/1/2016153011
281/1/2016153011
371/1/2016306024
371/1/2016306024
371/2/2016303014
381/3/2016153014
381/3/2016153014
471/3/2016303011
581/2/2016153011
581/2/2016153011
671/1/2016306023
671/1/2016306023
681/1/2016151513

 

So it is not necessary for the summary value to appear with the first drug date combination. 

 

Sorry again for the confusion. 

Super User
Posts: 10,028

Re: Calculations based on person, drug, AND date

Posted in reply to TXSASneophyte

To be honest, your question is quite hard to understand.

Assuming I know what you are looking for.

 

data have;
input ID  Drug_ID RX_FILL_DT : mmddyy10. Rx_Amt	;
format rx_fill_dt mmddyy10.;
cards;
1   7   1/1/2016    30
1   8   1/1/2016    15
1   8   1/2/2016    15
1   8   1/2/2016    15
2   8   1/1/2016    15
2   8   1/1/2016    15
3   7   1/1/2016    30
3   7   1/1/2016    30
3   7   1/2/2016    30
3   8   1/3/2016    15
3   8   1/3/2016    15
4   7   1/3/2016    30
5   8   1/2/2016    15
5   8   1/2/2016    15
6   7   1/1/2016    30
6   7   1/1/2016    30
6   8   1/1/2016    15
;
run;
proc sql;
create table want as
select *,ceil(sum(rx_amt)/30) as Aggreg_DispEventsperID
from 
(
 select *,sum(rx_amt) as sum_rx_amt,
  ceil(calculated sum_rx_amt/30) as dispense_event 
  from have
   group by id,drug_id,rx_fill_dt
) 
group by id
   ;
quit;
Contributor
Posts: 45

Re: Calculations based on person, drug, AND date

Really sorry about my bad explanations but you code is great; it really captures most of what I need!!! There is still a small issue with the rounding I was hoping you can help me with. 

 

When the "sum_rx_amount" is less than 30, than the result should round up for "dispense_event" (your code does this with the "CIEL" function). When the "sum_rx_amount" is greater than 30, than the result should round down for "dispense_event". Is there a way to tell SAS which function to use ("CIEL" OR "INT") depending on the result of summing "rx_amount"?     

 

The rest of your code is amazing! 

Solution
‎10-15-2016 01:47 PM
Super User
Posts: 10,028

Re: Calculations based on person, drug, AND date

Posted in reply to TXSASneophyte

Maybe ROUND() is what you are looking for .

 

 

data have;
input ID  Drug_ID RX_FILL_DT : mmddyy10. Rx_Amt	;
format rx_fill_dt mmddyy10.;
cards;
1   7   1/1/2016    30
1   8   1/1/2016    15
1   8   1/2/2016    15
1   8   1/2/2016    15
2   8   1/1/2016    15
2   8   1/1/2016    15
3   7   1/1/2016    30
3   7   1/1/2016    30
3   7   1/2/2016    30
3   8   1/3/2016    15
3   8   1/3/2016    15
4   7   1/3/2016    30
5   8   1/2/2016    15
5   8   1/2/2016    15
6   7   1/1/2016    30
6   7   1/1/2016    30
6   8   1/1/2016    15
;
run;
proc sql;
create table want as
select *,round(sum(rx_amt)/30) as Aggreg_DispEventsperID
from 
(
 select *,sum(rx_amt) as sum_rx_amt,
  round(calculated sum_rx_amt/30) as dispense_event 
  from have
   group by id,drug_id,rx_fill_dt
) 
group by id
   ;
quit;
Contributor
Posts: 45

Re: Calculations based on person, drug, AND date

The round function didn't capture what I needed but I just added a simple data step and got what I needed. But thank you so much for your code, I would still be lost without you; it definitely covered 99% of what I needed so I thank you so much!!! Smiley Very Happy

Contributor
Posts: 45

Re: Calculations based on person, drug, AND date

Quick update!

I made an error, the "ROUND" suggestion with your code captures what I need! Thanks for all your help!
☑ This topic is solved.

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

Discussion stats
  • 9 replies
  • 484 views
  • 0 likes
  • 3 in conversation