Proc Summary

[vnreddy]

Hi,

I have a detailed data as shown in below code, could someone help me with the summary I am looking for as shown in below output image.

data have;
input REGION $ CODE $ DATE :datetime20. TRAVEL QTY PRICE;
format date datetime20.;
datalines;
EAST	D1234TN		05MAY2022:07:03:13	14.38	19.12	56.98
EAST	D1234TN		05MAY2022:08:26:45	14.12	19.02	56.68
EAST	D1234TN		06MAY2022:15:11:02	23.31	18.74	72.52
EAST	E2689PV	  	04MAY2022:07:03:15	18.80	19.10	64.37
EAST	E2689PV		.					11.50	19.50	52.84
SOUTH	G1539GV		25MAY2022:08:49:37	39.46	19.70	146.57
SOUTH	G1539GV		19MAY2022:07:02:44	 8.26	 7.25	27.75
SOUTH	G2216NV		10MAY2022:11:07:03	16.59	19.98	90.91
;
run;

expected output

1. I want to derive count from codes for example D1234TN repeated 3 times in source so the count will be 3

2. I want to have have total_travel, total_qty, total_price from travel,qty,price columns

3. Avg_travel should be derived from total_travel/count against each code as shown in above image

4. Avg_qty and avg_price from total_qty/count and total_price/count

5. Finally, I want to show bolded row for total count of codes on a region wise, and total count, sum(total_travel), sum(total_qty), sum(total_price) and average(travel),average(qty),average(price)

Thanks & regards,

vnreddy.

[PeterClemmensen]

Can you show us your desired result given this data?

Makes it much easier to provide a usable code answer 🙂

[vnreddy]

Hi,

posted the expected output image

[PaigeMiller]

PROC SUMMARY cannot make text bold. You can do that in PROC REPORT. It also will not place a 2 in the CODE column, or order the results the way you want. You can use PROC SORT to get the proper ordering and a DATA step to place a 2 in the code column.

proc summary data=have;
    class region code;
    types region region*code;
    var travel qty price;
    output out=want mean= sum=/autoname;
run;

[Ksharp · Accepted solution]

data have;
input REGION $ CODE $ DATE :datetime20. TRAVEL QTY PRICE;
format date datetime20.;
datalines;
EAST	D1234TN		05MAY2022:07:03:13	14.38	19.12	56.98
EAST	D1234TN		05MAY2022:08:26:45	14.12	19.02	56.68
EAST	D1234TN		06MAY2022:15:11:02	23.31	18.74	72.52
EAST	E2689PV	  	04MAY2022:07:03:15	18.80	19.10	64.37
EAST	E2689PV		.					11.50	19.50	52.84
SOUTH	G1539GV		25MAY2022:08:49:37	39.46	19.70	146.57
SOUTH	G1539GV		19MAY2022:07:02:44	 8.26	 7.25	27.75
SOUTH	G2216NV		10MAY2022:11:07:03	16.59	19.98	90.91
;
run;

proc sql;
create table want as
select 1 as id,REGION ,CODE,count(*) as count,
 sum(TRAVEL) as total_TRAVEL,
 sum(QTY) as total_QTY,
 sum(PRICE) as total_PRICE,

 avg(TRAVEL) as avg_TRAVEL,
 avg(QTY) as avg_QTY,
 avg(PRICE) as avg_PRICE

 from have
  group by REGION ,CODE 

union all
select 2 as id,REGION ,put(count(distinct CODE),best. -l),count(*) as count,
 sum(TRAVEL) as total_TRAVEL,
 sum(QTY) as total_QTY,
 sum(PRICE) as total_PRICE,

 avg(TRAVEL) as avg_TRAVEL,
 avg(QTY) as avg_QTY,
 avg(PRICE) as avg_PRICE

 from have
  group by REGION  

order by 2,1
;
quit;