Sql summing

[SASdevAnneMarie]

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!

[PaigeMiller]

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

[Mazi]

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

[Patrick]

@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;

[SASdevAnneMarie]

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

[ballardw]

---

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

[PaigeMiller]

---

@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;

[Patrick]

---

@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;

[Kurt_Bremser]

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