topic ExcelXP tagset with Proc Tabulate in ODS and Base Reporting

ExcelXP tagset with Proc Tabulate

EdB — Fri, 29 Feb 2008 21:28:24 GMT

I think there is a feature with the ExcelXP tagset with Proc Tabulate that makes nice reports but merges cells in a way that makes working with the data in Excel difficult.

The example from the paper "ODS MARKUP: The SAS® Reports You've Always Dreamed Of" by Eric A. Gebhart has a example that combines tables in one sheet, which is exactly what I wanted to do. I used this approack and created very nice reports, but when I tried to pass the data to Chart, the data in the first row was in in the header row.

This example illustrated my problem. (from page 14 in Eric's paper)

This is what the table looks like
Predicted Actual
Region Division Product Type
East Consumer Furniture cell 1,1 cell 1,2
Office cell 2,1 cell 2,2

These are the cells that contain the data,

Predicted Actual
Region Division Product Type cell 1,1 cell 1,2
East Consumer Furniture
Office cell 2,1 cell 2,2

The XML shows this for the rows in question (I added breaks to show the data)

Region

Division

Product type

11081

12483

EAST

CONSUMER

FURNITURE

Does anyone know if there is a way to stop merging the data cells?
Thanks
Ed

Re: ExcelXP tagset with Proc Tabulate

Cynthia_sas — Fri, 29 Feb 2008 22:30:39 GMT

Hi, Ed:
I wasn't sure whether you didn't want the spanning or whether you actively wanted the region and division to repeat on every row. Either way, with PROC REPORT, you can duplicate the TABULATE table and there is no spanning of rows. You can see from the PROC REPORT, that I have used NOPRINT with REGION and DIVISION in order to be able to show computed items PREG and PDIV on every row.

I only did the worksheet for CANADA from Eric's example. Maybe this will help.

cynthia
[pre]
ods listing close;
ods path work.mytemp(update)
sasuser.templat(update)
sashelp.tmplmst(read);

proc template;
define style styles.XLStatistical;
parent = styles.Statistical;
style Header from Header /
borderwidth=2;
style RowHeader from RowHeader /
borderwidth=2
font_size=10pt;
style Data from Data /
borderwidth=2;
end;
run;

data prdsale;
set sashelp.prdsale;
Difference = actual-predict;
run;

proc sort data=prdsale;
by country region division year;
run;

ods tagsets.excelxp file='ExcelXP_report_sample09.xls'
style=XLStatistical
options(auto_subtotals='yes'
default_column_width='7, 10, 10, 7, 7'
frozen_rowheaders='yes'
sheet_interval='none'
sheet_name='Canada'
autofilter='all'
autofilter_table='2');
*;
* The output from the following two procs will be in a single worksheet
* with a user-specified name of 'Canada'.
*;

proc report data=prdsale nowd
style(summary)=RowHeader;
where country eq 'CANADA' and year eq 1993;
column region division preg pdiv prodtype predict actual;
define region/group noprint;
define division /group noprint;
define preg / computed 'Region'
style(column)=RowHeader;
define pdiv /computed 'Division'
style(column)=RowHeader;
define prodtype /group;
define predict /sum;
define actual / sum;
compute before region;
holdreg = region;
endcomp;
compute before division;
holddiv = division;
endcomp;
compute preg / character length=15;
preg = holdreg;
endcomp;
compute pdiv / character length=15;
pdiv = holddiv;
endcomp;
break after region / summarize;
rbreak after /summarize;
compute after region;
pdiv = 'Division Total';
endcomp;
compute after;
preg = 'Grand Total';
endcomp;
run;

proc print data=prdsale noobs label split='*';
where country eq 'CANADA' and year eq 1993;
id country region division;
var prodtype product quarter month year;
sum predict / style={tagattr='format:Currency'};
sum actual / style={tagattr='format:Currency'};
sum difference / style={tagattr='format:Currency formula:RC[-1]-RC[-2]'};
label prodtype = 'Product*Type'
predict = 'Predicted*Sales'
actual = 'Actual*Sales';
run;

ods tagsets.excelxp close;
[/pre]

Re: ExcelXP tagset with Proc Tabulate

EdB — Mon, 03 Mar 2008 18:06:21 GMT

Thanks Cynthia
I want the spanning so that this looks like an Excel Pivot table.

I was pretty sure that this could be done several ways, but the Tabulate seemed to do the trick with only a few lines of code.

Thanks again for your help
Ed