Help using Base SAS procedures

Totals in proc tabulate

Reply
Frequent Contributor
Posts: 131

Totals in proc tabulate


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

Super User
Posts: 17,750

Re: Totals in proc tabulate

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

Base SAS(R) 9.3 Procedures Guide, Second Edition

SAS Super FREQ
Posts: 8,740

Re: Totals in proc tabulate

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;


compare_two_tabulates.png
Ask a Question
Discussion stats
  • 2 replies
  • 173 views
  • 0 likes
  • 3 in conversation