Averages at detail and summary level

[sas33]

Hi , i have following detailed output dataset 

.

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

i wanted to summary dataset as something similar below

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

can you please help

[Reeza]

PROC TABULATE will do what you want.

[PaigeMiller · Accepted solution]

This is exactly what PROC SUMMARY does

Example code:

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

PROC REPORT will do similar things in this situation.

[Patrick]

@sas33 

Please post the same questions only once so that the whole discussion stays together in a single place.

More here: https://communities.sas.com/t5/SAS-Programming/Averages-summary-level/m-p/535965#M147227