topic Re: Calculations based on person, drug, AND date in SAS Programming

Calculations based on person, drug, AND date

TXSASneophyte — Thu, 13 Oct 2016 18:53:51 GMT

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

Re: Calculations based on person, drug, AND date

ballardw — Thu, 13 Oct 2016 19:45:09 GMT

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?

Re: Calculations based on person, drug, AND date

TXSASneophyte — Thu, 13 Oct 2016 19:54:54 GMT

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.

Re: Calculations based on person, drug, AND date

ballardw — Thu, 13 Oct 2016 20:02:21 GMT

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?

Re: Calculations based on person, drug, AND date

TXSASneophyte — Thu, 13 Oct 2016 20:13:06 GMT

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.

Re: Calculations based on person, drug, AND date

Ksharp — Fri, 14 Oct 2016 05:31:47 GMT

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;

Re: Calculations based on person, drug, AND date

TXSASneophyte — Fri, 14 Oct 2016 14:09:42 GMT

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!

Re: Calculations based on person, drug, AND date

Ksharp — Sat, 15 Oct 2016 03:03:51 GMT

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;

Re: Calculations based on person, drug, AND date

TXSASneophyte — Sat, 15 Oct 2016 17:46:46 GMT

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

Re: Calculations based on person, drug, AND date

TXSASneophyte — Mon, 17 Oct 2016 13:02:00 GMT

Quick update!

I made an error, the "ROUND" suggestion with your code captures what I need! Thanks for all your help!