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
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
@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 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.
@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;
@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;
Don't do reports with SQL. SAS provides much better tools (REPORT, SUMMARY, TABULATE) for this.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.