The SAS Output Delivery System and reporting techniques

Proc tabulate : create extra column with subtotals

Reply
Occasional Contributor
Posts: 10

Proc tabulate : create extra column with subtotals

Hi

 

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

B1

2

18

20

25

B2

1

4

5

C1

6

18

24

61

C2

8

29

37

D1

12

29

41

41

E1

67

202

269

451

E2

34

148

182

 

 

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.

 

Super User
Super User
Posts: 7,963

Re: Proc tabulate : create extra column with subtotals

Posted in reply to andreas_gryn

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.  

Ask a Question
Discussion stats
  • 1 reply
  • 355 views
  • 0 likes
  • 2 in conversation