Hello
I want to calculate aggregation simple statistics to different groups.
Is there a shorter code that can produce same results?
Maybe via proc format?
proc sql;
create table Want as
select Type,
count(*) as nr_rows format=comma28.,
sum(MSRP) as SUM_MSRP format=comma28.,
sum(Invoice) as SUM_Invoice format=comma28.
from sashelp.cars
group by Type
union all
select 'SUV+Sports' as Type,
count(*) as nr_rows format=comma28.,
sum(MSRP) as SUM_MSRP format=comma28.,
sum(Invoice) as SUM_Invoice format=comma28.
from sashelp.cars
where Type in ('SUV','Sports')
union all
select 'SUV+Hybrid' as Type,
count(*) as nr_rows format=comma28.,
sum(MSRP) as SUM_MSRP format=comma28.,
sum(Invoice) as SUM_Invoice format=comma28.
from sashelp.cars
where Type in ('SUV','Hybrid')
union all
select 'Hybrid+SUV+Sedan+Sports' as Type,
count(*) as nr_rows format=comma28.,
sum(MSRP) as SUM_MSRP format=comma28.,
sum(Invoice) as SUM_Invoice format=comma28.
from sashelp.cars
where Type in ('Hybrid','SUV','Sedan','Sports')
union all
select ' Truck+Wagon' as Type,
count(*) as nr_rows format=comma28.,
sum(MSRP) as SUM_MSRP format=comma28.,
sum(Invoice) as SUM_Invoice format=comma28.
from sashelp.cars
where Type in ('Truck','Wagon')
union all
select 'ALL' as Type,
count(*) as nr_rows format=comma28.,
sum(MSRP) as SUM_MSRP format=comma28.,
sum(Invoice) as SUM_Invoice format=comma28.
from sashelp.cars
;
quit;
proc format; value $type (multilabel notsorted) "SUV" = "SUV" "Wagon" = "Wagon" "Sports" = "Sports" "Sedan" = "Sedan" "Truck" = "Truck" "Hybrid" = "Hybrid" "SUV", "Truck", "Hybrid", "Wagon", "Sedan", "Sports" = "All" "SUV", "Sports" = "SUV+Sports" "SUV", "Hybrid" = "SUV+Hybrid" "Hybrid", "SUV", "Sedan", "Sports" = "Hybrid+SUV+Sedan+Sports" "Truck", "Wagon" = "Truck+Wagon"; run; proc means data=sashelp.cars completetypes nway; class type / mlf preloadfmt order=formatted exclusive; format type $type.; var msrp invoice; output out = want sum(msrp invoice)=/autoname; run;
can you try this ?
PROC SUMMARY/PROC MEANS with multi-label formats.
IMHO, PROC SUMMARY/PROC MEANS should always be the choice for computing a lot of statistics. IMHO, SQL should be the last choice for computing a lot of statistics.
proc format; value $type (multilabel notsorted) "SUV" = "SUV" "Wagon" = "Wagon" "Sports" = "Sports" "Sedan" = "Sedan" "Truck" = "Truck" "Hybrid" = "Hybrid" "SUV", "Truck", "Hybrid", "Wagon", "Sedan", "Sports" = "All" "SUV", "Sports" = "SUV+Sports" "SUV", "Hybrid" = "SUV+Hybrid" "Hybrid", "SUV", "Sedan", "Sports" = "Hybrid+SUV+Sedan+Sports" "Truck", "Wagon" = "Truck+Wagon"; run; proc means data=sashelp.cars completetypes nway; class type / mlf preloadfmt order=formatted exclusive; format type $type.; var msrp invoice; output out = want sum(msrp invoice)=/autoname; run;
can you try this ?
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
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.
Ready to level-up your skills? Choose your own adventure.