BookmarkSubscribeRSS Feed
EdB
Calcite | Level 5 EdB
Calcite | Level 5
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
2 REPLIES 2
Cynthia_sas
SAS Super FREQ
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]
EdB
Calcite | Level 5 EdB
Calcite | Level 5
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 722 views
  • 0 likes
  • 2 in conversation