topic Re: aggregation simple statistics to different groups in SAS Programming

aggregation simple statistics to different groups

Ronein — Wed, 13 Mar 2024 12:55:42 GMT

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;

Re: aggregation simple statistics to different groups

PaigeMiller — Wed, 13 Mar 2024 14:40:33 GMT

PROC SUMMARY/PROC MEANS with multi-label formats.

Example: https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.4/proc/n13dhme6o4ut3en1u8e58r16b3zf.htm#n13dhme6o4ut3en1u8e58r16b3zf

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.

Re: aggregation simple statistics to different groups

Mazi — Wed, 13 Mar 2024 15:11:14 GMT

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 ?