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

Hi, i have following data set , can you help in creating a desired output as below.

 

QuantityPharmacyBrand_namecostStrength
15OTHERDrug2$391.6330 MG
15OTHERDrug2$382.3830 MG
8PHARMACY2Drug2$213.3230 MG
30OTHERDrug2$1,553.8060 MG
44PHARMACY3Drug2$3,450.5490 MG
44PHARMACY3Drug2$3,450.5490 MG
44PHARMACY3Drug2$3,450.5490 MG
31OTHERDrug2$824.2530 MG
31OTHERDrug2$802.8530 MG
31OTHERDrug2$802.8530 MG
15PHARMACY1Drug2$400.4430 MG
30OTHERDrug2$764.7530 MG
30OTHERDrug2$779.2730 MG
23OTHERDrug2$586.3130 MG
30OTHERDrug2$782.4730 MG
30OTHERDrug2$782.4730 MG
30OTHERDrug2$782.4730 MG
30OTHERDrug2$800.4930 MG
4OTHERDrug2$101.9730 MG
30PHARMACY1Drug2$800.4930 MG
30OTHERDrug2$776.1830 MG
30OTHERDrug2$779.8630 MG
60OTHERDrug2$3,127.5560 MG
30OTHERDrug2$764.7530 MG
15OTHERDrug2$382.3830 MG
30OTHERDrug2$776.9530 MG
60OTHERDrug2$1,140.9330 MG
30PHARMACY1Drug2$800.4930 MG
60PHARMACY1Drug2$3,200.7460 MG
12PHARMACY2Drug2$319.4930 MG
30OTHERDrug2$1,529.5060 MG
16OTHERDrug2$420.0830 MG
30OTHERDrug2$764.7530 MG
30PHARMACY2Drug2$797.2130 MG
7OTHERDrug2$205.2330 MG
7OTHERDrug2$205.2330 MG
7OTHERDrug2$205.2330 MG
13OTHERDrug2$331.3930 MG
4OTHERDrug2$107.1630 MG
15PHARMACY1Drug2$400.4430 MG
15PHARMACY1Drug2$400.4430 MG
13OTHERDrug2$307.8530 MG
30OTHERDrug2$1,529.5060 MG
30PHARMACY1Drug2$800.4930 MG
30PHARMACY1Drug2$800.4930 MG
30PHARMACY1Drug2$800.4930 MG
30OTHERDrug2$782.4730 MG
30OTHERDrug2$1,553.8060 MG
30OTHERDrug2$1,553.8060 MG
15OTHERDrug2$1,127.0190 MG
15OTHERDrug2$1,127.0190 MG
30OTHERDrug2$779.8630 MG
30OTHERDrug2$1,545.9660 MG
15OTHERDrug2$400.4430 MG
30PHARMACY1Drug2$1,600.5760 MG
30PHARMACY1Drug2$1,600.5760 MG
45OTHERDrug2$1,147.1330 MG
30OTHERDrug2$785.0530 MG
30PHARMACY1Drug2$800.4930 MG
30OTHERDrug2$782.4730 MG
30OTHERDrug2$785.0530 MG
30PHARMACY1Drug2$1,600.5760 MG
8OTHERDrug2$212.7130 MG
8OTHERDrug2$212.7130 MG
15OTHERDrug2$391.6330 MG
30OTHERDrug2$764.7530 MG
20OTHERDrug2$518.0030 MG
20OTHERDrug2$518.0030 MG

 

output desired

YearBrandStrengthPharmacy NameTotal CostAvg. Units Dispensed
2019Drug2  $48,377.8529
2019Drug230 MG $16,905.3524
 Drug230 MGOther$12,292.4226
 Drug230 MGPharmacy1$3,602.4027
 Drug230 MGPharmacy2$1,010.5319
2019Drug260 MG $18,866.8636
 Drug260 MGOther$10,864.4135
 Drug260 MGPharmacy1$8,002.4538
2019Drug290 MG $12,605.6430
 Drug290 MGPharmacy3$10,351.6244
 Drug290 MGOther$2,254.0215
1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

@sas33 wrote:

i am adding up 23.325+25+16.66666667 and dividing by 3 for 30MG.

 

same for 90mg. adding 44+15 and dividing by 2.


The result Proc Summary returns is correct, the formula you're using isn't.

Just summing averages and then dividing these averages by the number of categories won't return the correct result for the next higher hierarchy level. You would need to multiply the averages by the number of observations they are based on.

(44*3 + 15*2) /5 = 32.4

 

Look at your detail data and you will understand.

 

Capture.JPG

 

 

View solution in original post

6 REPLIES 6
PaigeMiller
Diamond | Level 26

Either PROC SUMMARY or PROC REPORT can do this

 

Sample code:

 

proc summary data=have;
    class brand strength pharmacy;
    types brand brand*strength brand*strength*pharmacy;
    var cost quantity;
    output out=want sum(cost)=total_cost mean(units)=avg_units_dispensed;
run;

I left out the variable YEAR from the output because it is not a variable in the input.

--
Paige Miller
sas33
Calcite | Level 5

Thank you, Paige Miller.

 

i am expecting following result..and i am seeing a variance in average. how do i achieve below numbers

 

Brand_nameStrengthPharmacycostAverage_qty 
Drug2  $64,168.6825.88235294 
Drug230 MG $31,166.6823.23076923 
Drug260 MG $20,396.3635.45454545 
Drug290 MG $12,605.6432.4 
Drug230 MGOTHER$23,832.4023.32521.66389
Drug230 MGPHARMACY1$6,004.2625 
Drug230 MGPHARMACY2$1,330.0216.66666667 
Drug260 MGOTHER$12,393.9134.28571429 
Drug260 MGPHARMACY1$8,002.4537.5 
Drug290 MGOTHER$2,254.021529.5
Drug290 MGPHARMACY3$10,351.6244 
ballardw
Super User

@sas33 wrote:

Thank you, Paige Miller.

 

i am expecting following result..and i am seeing a variance in average. how do i achieve below numbers

 

Brand_name Strength Pharmacy cost Average_qty  
Drug2     $64,168.68 25.88235294  
Drug2 30 MG   $31,166.68 23.23076923  
Drug2 60 MG   $20,396.36 35.45454545  
Drug2 90 MG   $12,605.64 32.4  
Drug2 30 MG OTHER $23,832.40 23.325 21.66389
Drug2 30 MG PHARMACY1 $6,004.26 25  
Drug2 30 MG PHARMACY2 $1,330.02 16.66666667  
Drug2 60 MG OTHER $12,393.91 34.28571429  
Drug2 60 MG PHARMACY1 $8,002.45 37.5  
Drug2 90 MG OTHER $2,254.02 15 29.5
Drug2 90 MG PHARMACY3 $10,351.62 44  

You will have to describe you arrive at 21.66389 and 29.5 in your values.

If 23.23076923 and 32.4 are not the values of average then what do you expect and how did you calculate the expected value?

 

 

 

sas33
Calcite | Level 5

i am adding up 23.325+25+16.66666667 and dividing by 3 for 30MG.

 

same for 90mg. adding 44+15 and dividing by 2.

PaigeMiller
Diamond | Level 26

In the data you showed at the beginning of this thread, there is no 44 for 90 MG and pharmacy other.

 

In the data you showed at the beginning of this thread, there are a lot more than 3 records with 30 MG and pharmacy OTHER.

--
Paige Miller
Patrick
Opal | Level 21

@sas33 wrote:

i am adding up 23.325+25+16.66666667 and dividing by 3 for 30MG.

 

same for 90mg. adding 44+15 and dividing by 2.


The result Proc Summary returns is correct, the formula you're using isn't.

Just summing averages and then dividing these averages by the number of categories won't return the correct result for the next higher hierarchy level. You would need to multiply the averages by the number of observations they are based on.

(44*3 + 15*2) /5 = 32.4

 

Look at your detail data and you will understand.

 

Capture.JPG

 

 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 6 replies
  • 693 views
  • 0 likes
  • 4 in conversation