Averages summary level

[sas33]

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

Quantity	Pharmacy	Brand_name	cost	Strength
15	OTHER	Drug2	$391.63	30 MG
15	OTHER	Drug2	$382.38	30 MG
8	PHARMACY2	Drug2	$213.32	30 MG
30	OTHER	Drug2	$1,553.80	60 MG
44	PHARMACY3	Drug2	$3,450.54	90 MG
44	PHARMACY3	Drug2	$3,450.54	90 MG
44	PHARMACY3	Drug2	$3,450.54	90 MG
31	OTHER	Drug2	$824.25	30 MG
31	OTHER	Drug2	$802.85	30 MG
31	OTHER	Drug2	$802.85	30 MG
15	PHARMACY1	Drug2	$400.44	30 MG
30	OTHER	Drug2	$764.75	30 MG
30	OTHER	Drug2	$779.27	30 MG
23	OTHER	Drug2	$586.31	30 MG
30	OTHER	Drug2	$782.47	30 MG
30	OTHER	Drug2	$782.47	30 MG
30	OTHER	Drug2	$782.47	30 MG
30	OTHER	Drug2	$800.49	30 MG
4	OTHER	Drug2	$101.97	30 MG
30	PHARMACY1	Drug2	$800.49	30 MG
30	OTHER	Drug2	$776.18	30 MG
30	OTHER	Drug2	$779.86	30 MG
60	OTHER	Drug2	$3,127.55	60 MG
30	OTHER	Drug2	$764.75	30 MG
15	OTHER	Drug2	$382.38	30 MG
30	OTHER	Drug2	$776.95	30 MG
60	OTHER	Drug2	$1,140.93	30 MG
30	PHARMACY1	Drug2	$800.49	30 MG
60	PHARMACY1	Drug2	$3,200.74	60 MG
12	PHARMACY2	Drug2	$319.49	30 MG
30	OTHER	Drug2	$1,529.50	60 MG
16	OTHER	Drug2	$420.08	30 MG
30	OTHER	Drug2	$764.75	30 MG
30	PHARMACY2	Drug2	$797.21	30 MG
7	OTHER	Drug2	$205.23	30 MG
7	OTHER	Drug2	$205.23	30 MG
7	OTHER	Drug2	$205.23	30 MG
13	OTHER	Drug2	$331.39	30 MG
4	OTHER	Drug2	$107.16	30 MG
15	PHARMACY1	Drug2	$400.44	30 MG
15	PHARMACY1	Drug2	$400.44	30 MG
13	OTHER	Drug2	$307.85	30 MG
30	OTHER	Drug2	$1,529.50	60 MG
30	PHARMACY1	Drug2	$800.49	30 MG
30	PHARMACY1	Drug2	$800.49	30 MG
30	PHARMACY1	Drug2	$800.49	30 MG
30	OTHER	Drug2	$782.47	30 MG
30	OTHER	Drug2	$1,553.80	60 MG
30	OTHER	Drug2	$1,553.80	60 MG
15	OTHER	Drug2	$1,127.01	90 MG
15	OTHER	Drug2	$1,127.01	90 MG
30	OTHER	Drug2	$779.86	30 MG
30	OTHER	Drug2	$1,545.96	60 MG
15	OTHER	Drug2	$400.44	30 MG
30	PHARMACY1	Drug2	$1,600.57	60 MG
30	PHARMACY1	Drug2	$1,600.57	60 MG
45	OTHER	Drug2	$1,147.13	30 MG
30	OTHER	Drug2	$785.05	30 MG
30	PHARMACY1	Drug2	$800.49	30 MG
30	OTHER	Drug2	$782.47	30 MG
30	OTHER	Drug2	$785.05	30 MG
30	PHARMACY1	Drug2	$1,600.57	60 MG
8	OTHER	Drug2	$212.71	30 MG
8	OTHER	Drug2	$212.71	30 MG
15	OTHER	Drug2	$391.63	30 MG
30	OTHER	Drug2	$764.75	30 MG
20	OTHER	Drug2	$518.00	30 MG
20	OTHER	Drug2	$518.00	30 MG

output desired

Year	Brand	Strength	Pharmacy Name	Total Cost	Avg. Units Dispensed
2019	Drug2			$48,377.85	29
2019	Drug2	30 MG		$16,905.35	24
	Drug2	30 MG	Other	$12,292.42	26
	Drug2	30 MG	Pharmacy1	$3,602.40	27
	Drug2	30 MG	Pharmacy2	$1,010.53	19
2019	Drug2	60 MG		$18,866.86	36
	Drug2	60 MG	Other	$10,864.41	35
	Drug2	60 MG	Pharmacy1	$8,002.45	38
2019	Drug2	90 MG		$12,605.64	30
	Drug2	90 MG	Pharmacy3	$10,351.62	44
	Drug2	90 MG	Other	$2,254.02	15

[PaigeMiller]

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.

[sas33]

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

[ballardw]

---

@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]

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]

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.

[Patrick · Accepted solution]

---

@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.