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 ?
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.