Proc tabulate : create extra column with subtotals

andreas_gryn — Fri, 05 Feb 2016 12:37:52 GMT

I created a report on a simple dataset using tabulate.

Now I want to add a column with subtotals.

The subtotal needs to be grouped based on the first position of the valuees in the first column.

I can create an extra variable in my dataset with the result.

	2014	2015	Totaal
A1	0	13	13	13
A2	0	0	0	13
B1	2	18	20	25
B2	1	4	5	25
C1	6	18	24	61
C2	8	29	37	61
D1	12	29	41	41
E1	67	202	269	451
E2	34	148	182	451
…	…		…	…

PROC TABULATE DATA=akkoord_p_r_age ;
CLASS csymp nr_year;
VAR aantal;
KEYLABEL SUM='';
TABLE csymp='' ALL=&&tot_&taal ,((nr_year='' ALL="Totaal &year_from - &year_to"))*aantal=''*(sum=''*f=10. colpctsum=''*f=pctpic7.2)
/BOX="" MISSTEXT=' ';
RUN;
QUIT;

Does anybody have an idea on how to do this easily?

Thanks in advance.

Re: Proc tabulate : create extra column with subtotals

RW9 — Fri, 05 Feb 2016 13:20:38 GMT

Hi,

Some tips first. Use the code window - in post a message its the {i} button above - this retains spacing and such like. Code formatting is also very important. Your code is very hard to read - mixed casings, line breaks all over, no indetation, and there is a whole lot of macro variables/code in there. You also haven't provided any test data (in the form of a datastep) which would give us something to run code on. I have made a best guess below, this is based on the fact that I personally don't really use proc tabulate, and prefer to do summaries using other methods:

data have;
  input col1 $ year res;
  cat=substr(col1,1,1);
datalines;
A1 2014 1
A1 2014 5
A1 2015 2
A1 2016 8
A2 2014 10
A2 2014 3
A2 2015 4
;
run;

proc sql;
  create table WANT as
  select  distinct 
          CAT,
          COL1,
          (select sum(RES) from HAVE where CAT=A.CAT and COL1=A.COL1 and YEAR=2014) as YEAR2014, 
          (select sum(RES) from HAVE where CAT=A.CAT and COL1=A.COL1 and YEAR=2015) as YEAR2015,
          (select sum(RES) from HAVE where CAT=A.CAT and COL1=A.COL1 and YEAR=2016) as YEAR2016,
          (select sum(RES) from HAVE where CAT=A.CAT and COL1=A.COl1) as TOTAL,
          (select sum(RES) from HAVE where CAT=A.CAT) as TOTAL_CAT 
  from    HAVE A;
quit;

proc report data=want...;
run;

As you will see, you will need another variable whichever way you go to identify which grouping of data to sum.

topic Proc tabulate : create extra column with subtotals in ODS and Base Reporting

Proc tabulate : create extra column with subtotals

Re: Proc tabulate : create extra column with subtotals