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.
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;
Can you show us your desired result given this data?
Makes it much easier to provide a usable code answer 🙂
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.