The SAS Output Delivery System and reporting techniques

ExcelXP tagset with Proc Tabulate

Reply
Occasional Contributor EdB
Occasional Contributor
Posts: 9

ExcelXP tagset with Proc Tabulate

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
SAS Super FREQ
Posts: 8,740

Re: ExcelXP tagset with Proc Tabulate

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]
Occasional Contributor EdB
Occasional Contributor
Posts: 9

Re: ExcelXP tagset with Proc Tabulate

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
Ask a Question
Discussion stats
  • 2 replies
  • 183 views
  • 0 likes
  • 2 in conversation