BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
TXSASneophyte
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

9 REPLIES 9
ballardw
Super User

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?

TXSASneophyte
Obsidian | Level 7

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. 

ballardw
Super User

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?

TXSASneophyte
Obsidian | Level 7

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. 

Ksharp
Super User

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;
TXSASneophyte
Obsidian | Level 7

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! 

Ksharp
Super User

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;
TXSASneophyte
Obsidian | Level 7

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

TXSASneophyte
Obsidian | Level 7
Quick update!

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 9 replies
  • 1319 views
  • 0 likes
  • 3 in conversation