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;
... View more
