Help with PROC Tabulate

[deleted_user]

Hi, I'm a newbie so sorry if there is an obvious answer to this.

I'm using Proc Tabulate in SASV8 with 3 class variables and 2 var variable.

I am using the table statement

TABLE class1*class2, class3*var1*Mean

Class2 is therefore nesting within Class1 which is fine but when I export the data using ODS the column for Class1 only displays each value of Class1 once, rather than several times for each value of class2. The same happens when I output the data to the output window.

e.g. I get:
Class1a Class2a
[blankcell]Class2b
[blankcell]Class2c
Class1b Class2a
[blankcell]Class2b etc

Instead of:
Class1a Class2a
Class1a Class2b
Class1a Class2c
Class1b Class2a
Class1b Class2b etc

I need to have the second way of formatting the data for my export but can't find a way to do it - any ideas?

Thanks
Rob

[Cynthia_sas]

Hi:
Proc Tabulate is trying to do you a favor by blanking out those row header areas. Most of the time, that's how folks want the report output to look. In the instance where you don't want that particular feature, you have to think outside the box or outside the ODS sandwich.

For example, if you get PROC TABULATE to do all the work of creating your summary information (using ODS OUTPUT), then you could use PROC PRINT on the output dataset from PROC TABULATE and the information for your CLASS1 variable would be on every line (this is example 1 in the code below). An issue with Example 1 is that you no longer have the TABULATE structure with your CLASS3 variables going across the COLUMNS -- I don't know whether that's acceptable to you or not. In my code, REGION is being repeated on every line -- it is the equivalent of your CLASS1 variable.

Or you could use PROC REPORT to generate your crosstabular report using GROUP and ACROSS variables instead of using TABULATE at all. Normally, PROC REPORT tries to do you the same favor that TABULATE does (blanks out repetitious variable values) -- but the good thing with PROC REPORT is that you can COMPUTE a new report column that is based on the values of an existing column. If you grab your variable value of interest at the beginning of a group, then you can assign it to your computed report column as shown in example 2 below.

Note that these examples use SASHELP.SHOES and so you should be able to run the program code. You said that you were "exporting" the data using ODS -- when you use ODS, you are not technically "exporting" (only PROC EXPORT does that), but I figured you were using either ODS HTML or ODS CSVALL to create files (probably for Excel) from your report procedure output. Report procedure output is BASED on data, but is a report about the data -- it could be a summary report, for example, where 1 report row represented the consolidation of many rows of your original data.

If you have further questions about the use of PROC REPORT vs PROC TABULATE, there are excellent examples of both in the SAS documentation and in many books by users available through SAS Publishing. Tech Support can also help you with questions about how both procedures can work to generate the particular report/output you need.

cynthia
[pre]

** Example 1;
ods listing close;
ods output table=work.tabout;
proc tabulate data=sashelp.shoes;
title 'TABULATE directly to data set';
class region product subsidiary;
var sales;
where region = 'Asia';
table region*product,
subsidiary*sales*mean;
run;

ods html3 file='c:\temp\ex1_html.xls' style=sasweb;
ods csvall file='c:\temp\ex1_comma.csv';

proc print data=work.tabout(drop=_table_ _page_ _type_) noobs;
title '1) PRINT on output from proc tabulate';
title2 'But your structure IS different -- this might be OK';
run;

ods _all_ close;

** Example 2;
ods html3 file='c:\temp\ex2_html.xls' style=sasweb;
ods csvall file='c:\temp\ex2_comma.csv';

proc report data=sashelp.shoes
nowd;
where region = 'Asia';
title '2) REPORT on SASHELP.SHOES with ACROSS usage';
column region prtreg product sales,subsidiary;
define region / group noprint;
define prtreg / computed 'Region';
define product / group;
define subsidiary /across ' ';
define sales/ mean ' ';
compute before region;
holdreg = region;
endcomp;
compute prtreg / character length=25;
prtreg = holdreg;
endcomp;
run;

ods _all_ close;
ods listing;
title;
footnote;
[/pre]

[deleted_user]

Hi

That's seems to have worked with a few tweaks, thanks. I needed to have the class variables listed in every cell because the user wants to have the data in a pivot table in excel and without the class variables in every cell you can't get the data into a pivot table without a lot of manual work. The fact that it merges cells is also a pain, although some VBA can be used to remove that in Excel.

Thanks
Rob

[deleted_user]

RobH

since you want no formatting, only data, and for just a pivot table, would not the output data set from proc means be more suitable?

For "TABLE class1*class2, class3*var1*Mean", the corresponding proc means code would be something like[pre]proc means data= {whatever} missing /* see below at Note1 */ ;
class class1 class2 class3 ;
var var1 ;
output out= mean_data_set mean= ;
run;[/pre] Note1: the missing option on the PROC statement ensures your output includes any data with missing values of class variables.
Note2: The output data set contains in order, the columns
BY variables
CLASS variables
_TYPE_ indicates the combination of class variables (7 implies all 3 were used, see the doc for a better description)
_FREQ_ counts the rows of input data set found with that class combination
VAR variables (in the syntax above the value in the VAR variables on the output will be the MEANs)
Note3: the output data set is sorted in the order
BY variables, _TYPE_, CLASS variables

Although you need no BY variables, I left in their description in case that might help.

There are many convenient ways of obtaining multiple statistics and/or multiple analysis variables, but your need seems limited.

Would that not provide what you need for your pivot table ?

PeterC