topic Averages summary level in SAS Programming

Averages summary level

sas33 — Fri, 15 Feb 2019 19:08:44 GMT

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

Re: Averages summary level

PaigeMiller — Fri, 15 Feb 2019 19:12:18 GMT

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.

Re: Averages summary level

sas33 — Fri, 15 Feb 2019 20:16:41 GMT

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

Re: Averages summary level

ballardw — Fri, 15 Feb 2019 21:15:45 GMT

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

Re: Averages summary level

sas33 — Fri, 15 Feb 2019 21:22:46 GMT

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.

Re: Averages summary level

PaigeMiller — Fri, 15 Feb 2019 22:21:31 GMT

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.

Re: Averages summary level

Patrick — Fri, 15 Feb 2019 22:44:38 GMT

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