Solved
Contributor
Posts: 45

# 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).

 ID Drug_ID RX_FILL_DT Rx_Amt Aggregate_Rx_Amt_per_DT Dispense_Event Aggreg._Disp.Events per ID 1 7 1/1/2016 30 30 1 3 1 8 1/1/2016 15 15 1 … 1 8 1/2/2016 15 30 1 … 1 8 1/2/2016 15 … … … 2 8 1/1/2016 15 30 1 1 2 8 1/1/2016 15 … … … 3 7 1/1/2016 30 60 2 4 3 7 1/1/2016 30 … … … 3 7 1/2/2016 30 30 1 … 3 8 1/3/2016 15 30 1 … 3 8 1/3/2016 15 ... ... ... 4 7 1/3/2016 30 30 1 1 5 8 1/2/2016 15 30 1 1 5 8 1/2/2016 15 … … … 6 7 1/1/2016 30 60 2 3 6 7 1/1/2016 30 … … … 6 8 1/1/2016 15 15 1 …

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,849

## Re: Calculations based on person, drug, AND date

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;``````

All Replies
Super User
Posts: 13,926

## Re: Calculations based on person, drug, AND date

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: 13,926

## Re: Calculations based on person, drug, AND date

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.

 ID Drug_ID RX_FILL_DT Rx_Amt Aggregate_Rx_Amt_per_DT Dispense_Event Aggreg._Disp.Events per ID 1 7 1/1/2016 30 30 1 3 1 8 1/1/2016 15 15 1 3 1 8 1/2/2016 15 30 1 3 1 8 1/2/2016 15 30 1 3 2 8 1/1/2016 15 30 1 1 2 8 1/1/2016 15 30 1 1 3 7 1/1/2016 30 60 2 4 3 7 1/1/2016 30 60 2 4 3 7 1/2/2016 30 30 1 4 3 8 1/3/2016 15 30 1 4 3 8 1/3/2016 15 30 1 4 4 7 1/3/2016 30 30 1 1 5 8 1/2/2016 15 30 1 1 5 8 1/2/2016 15 30 1 1 6 7 1/1/2016 30 60 2 3 6 7 1/1/2016 30 60 2 3 6 8 1/1/2016 15 15 1 3

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,849

## Re: Calculations based on person, drug, AND date

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,849

## Re: Calculations based on person, drug, AND date

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!!!

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.