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
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;
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?
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.
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?
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.
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;
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!
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;
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!!! 😄
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.