topic Re: Sql summing in SAS Programming

Sql summing

SASdevAnneMarie — Wed, 13 Mar 2024 19:25:40 GMT

Hello Experts,

I would like to calculate sum (Quantity) by Id (group by Id) and the sum(Quantity) as total, but I don’t know how to do it in the same proc sql statement.

Thank you for your help!

Re: Sql summing

PaigeMiller — Wed, 13 Mar 2024 19:37:42 GMT

I always prefer doing this in PROC SUMMARY.

PROC SUMMARY data = have;
var quantity.
class id;
output out=want sum=;
run;

The row in WANT with _type_=0 is the overall total

Re: Sql summing

Mazi — Wed, 13 Mar 2024 19:49:38 GMT

proc sql;
	select type, sum(msrp) as sum
	from sashelp.cars
	group by type
	outer union corr
	select 
	"ALL" as type, sum(msrp) as sum
	from sashelp.cars;
quit;

To do it in one statement you’d need to use the union set operator.

First with the group by, and then without

Re: Sql summing

Patrick — Thu, 14 Mar 2024 02:22:55 GMT

@SASdevAnneMarie As demonstrated Proc Summary can do this in one go and though will likely perform better.

Below the coding approach using a SQL SET operator as documented here. Important is to also use the ALL keyword so the SQL doesn't try to dedup the result.

proc sql;
	select type, sum(msrp) as sum
	from sashelp.cars
	group by type
	union corr ALL
	select 
	"ALL" as type, sum(msrp) as sum
	from sashelp.cars;
quit;

Re: Sql summing

SASdevAnneMarie — Thu, 14 Mar 2024 16:51:11 GMT

Thank you, Patrick,
How could I have the total value in column (not just in the last line), please ?

Re: Sql summing

ballardw — Thu, 14 Mar 2024 17:53:23 GMT

@SASdevAnneMarie wrote:
Thank you, Patrick,
How could I have the total value in column (not just in the last line), please ?

That starts sounding more like a report.

Perhaps time to share some example data and the expected result for that data.

Re: Sql summing

PaigeMiller — Thu, 14 Mar 2024 18:43:21 GMT

@SASdevAnneMarie wrote:
Thank you, Patrick,
How could I have the total value in column (not just in the last line), please ?

It's always a good idea to tell us -- or even better, show us -- what you want, in your first post in the thread. You should do that from now on. You will get faster and better answers.

PROC SUMMARY data = have;
var quantity.
class id;
output out=want sum=;
run;

data want2;
    if _n_=1 then set want(where=(_type_=0) rename=(quantity=total));
    set want(where=(_type_=1));
run;

Re: Sql summing

Patrick — Thu, 14 Mar 2024 22:50:17 GMT

@SASdevAnneMarie wrote:
Thank you, Patrick,
How could I have the total value in column (not just in the last line), please ?

SQL is likely no more the ideal syntax for this requirement - but here you go.

proc sql;
  select type, group_sum, sum(group_sum) as grand_total
  from
    (
    	select type, sum(msrp) as group_sum
    	from sashelp.cars
    	group by type
    )
	union corr ALL
	select 
	"ALL" as type, sum(msrp) as group_sum, sum(msrp) as grand_total
	from sashelp.cars;
    ;
quit;

Re: Sql summing

Kurt_Bremser — Fri, 15 Mar 2024 07:17:19 GMT

Don't do reports with SQL. SAS provides much better tools (REPORT, SUMMARY, TABULATE) for this.