How to calculate sum of values per column except one column to have total values per each column in PROC SQL / SAS Enterprise Guide?

Question

I have table (with only numric columns) in SAS Enterprise Guide like below:

YEAR   | COL1  | COL2
-------|-------|------
2021   | 20    | 10
2022   | 25    | 30
2023   | 5     | 1

And I need to sum values in each column except column "YEAR" to have total value per column. So I need something like below:

YEAR   | COL1  | COL2
-------|-------|------
2021   | 20    | 10
2022   | 25    | 30
2023   | 5     | 1
TOTAL  | 50    | 41

How can I do that in SAS Enterprise Guide / PROC SQL ?

Answer 1

If you need only summed values you can make a new table:

data have;
    input year col1 col2;
    datalines;
2021 20 10
2022 25 30
2023 5 1
;
quit;



proc sql;
    create table want as
        select sum(col1) as total_col1, sum(col2) as total_col2
            from have
    ;
quit;

If you want it in original table (I have no idea why you would like that) you can do it by firstly creating a new table for totals, and adding it to your old table. But you won't be able to have 'TOTAL' in your YEAR column as it should be num type. So I just left YEAR as missing in that row. That second approach is like this:

proc sql;
    create table want as
        select . as YEAR, sum(col1) as col1, sum(col2) as col2
            from have
    ;
quit;

data want2;
    set have want;
run;

Answer 2

So that looks like a REPORT and not a DATASET. So use a reporting procedure. Examples:

data have;
  input YEAR COL1 COL2;
cards;
2021 20  10
2022 25  30
2023 5   1
;

proc print data=have;
  id year ;
  sum col1 col2 ;
run;

proc report data=have;
  define year / order ;
  rbreak after / summarize;
run;