topic Re: Totals in proc tabulate in SAS Procedures

Totals in proc tabulate

Q1983 — Mon, 09 Dec 2013 21:17:12 GMT

data test (keep=loan process_date);

set test2;

by ln_no;

run;

produces dataset results

loan process_date

1 11/19/2013

2 11/19/2013

3 10/6/2013

4 10/6/2013

I converted the process_date to a character to use in as a header

data test3;

set test;

Completion=put(process_date,mddyy10.);

run;

Now I do a proc tabulate to view process_date as a column header and count the number of loans

proc tabulate data= test3 out=test4;

class Completion;;

var loan;

table Completion;

run;

Results in

10/6/2013 11/19/2013

2 2

Now I want to add a bottom line row total and an across column total like this

10/6/2013 11/19/2013 Total

2 2 4

Total 2 2 4

How can I accomplish this. Is it best to use another descriptive statistic

Re: Totals in proc tabulate

Reeza — Mon, 09 Dec 2013 21:33:17 GMT

Usually the ALL keyword will get you there. See the examples in the documentation

Base SAS(R) 9.3 Procedures Guide, Second Edition

Re: Totals in proc tabulate

Cynthia_sas — Tue, 10 Dec 2013 00:18:20 GMT

Hi, and in addition to Reeza's good suggestion to look in the doc for how to use ALL, there are a couple of other points:

1) you do not need to make your date variable a character variable for it to be in the column headers. For example, SASHELP.PRDSALE has a MONTH variable that is actually a date value. For either report created with the code below, MONTH is numeric and is formatted with DATE9. -- still a number. Using MONTH in the CLASS statement is what tells TABULATE to use it for column headers and not for statistics.

2) See Report 1 output. The final "row" total doesn't make sense for Report 1, but makes more sense for Report 2.

3) Look specifically in the doc for how to use the CLASS and VAR statements as well as the ALL keyword for totals or summary statistics.

Cynthia

title; footnote;

ods _all_ close;

** MONTH is numeric variable representing the DATE, formatted by default to MONNAME3.;

** change the format to DATE9. to see the date values;

ods html file='c:\temp\usetab1_col.html' style=sasweb;

proc tabulate data= sashelp.prdsale;

title '1) Bottom line Row Total does not make sense in this table';

where month le '30jun1993'd;

class month;

table n all, month all/ row=float;

format month date9.;

keylabel all='Total' n=' ';

keyword all / style={vjust=b};

label month='Date';

run;

proc tabulate data= sashelp.prdsale;

title '2) With a Row dimension, a grand total makes more sense';

where month le '30jun1993'd;

class product month;

table product all,

month*n all*n /box='2 dimension table' ;

format month date9.;

keylabel all='Total' n=' ';

keyword all / style={vjust=b};

label month='Date';

run;

ods html close;

title; footnote;