topic Re: tagsets.excelxp in ODS and Base Reporting

tagsets.excelxp

deleted_user — Mon, 17 May 2010 19:52:45 GMT

I'm in the process of generating a bunch of reports all on one spreadsheet, with each report on one sheet. Most of these reports have two reports within one sheet which prevents me from simply using the OUT= option in proc report and then proc exporting.

I'm playing with this tagsets.excelxp process and I am running into trouble after a "successful" run within SAS Enterprise Guide (I also tried it in 9.1). I get a Problems During Load window when trying to open the output in EXCEL. What is the cause of this issue?

Any way to create multiple sheets in a workbook without using tagsets.excelxp?

/* Testing sheet names issue*/

options ls=256 ps=40;
ods tagsets.excelxp file=************hidden********************.xls' style = sasweb
options( frozen_headers='yes'
orientation='landscape'
sheet_name='MGCN'
);
proc report data = AA.CURRPCP1 nocenter nowindows missing spacing=2 split = "@"
style(report)={font_face=times font_size=1 rules=none frame=void borderwidth=0 background=_undef_}
style(header)={font_face=times font_size=1 rules=none frame=void borderwidth=0 background=_undef_ bordercolor=none}
;
column specialty ATL BER BUR CAM CAP CUM ESS GLO HUD HUN MER MID MON MOR OCE PAS SAL
SOM SUS UNI WAR TOTAL;

define specialty/group width = 100 'SPECIALTY';
define ATL/analysis width=6 format=comma6.0 'ATL';
define BER/analysis width=6 format=comma6.0 'BER';
define BUR/analysis width=6 format=comma6.0 'BUR';
define CAM/analysis width=6 format=comma6.0 'CAM';
define CAP/analysis width=6 format=comma6.0 'CAP';
define CUM/analysis width=6 format=comma6.0 'CUM';
define ESS/analysis width=6 format=comma6.0 'ESS';
define GLO/analysis width=6 format=comma6.0 'GLO';
define HUD/analysis width=6 format=comma6.0 'HUD';
define HUN/analysis width=6 format=comma6.0 'HUN';
define MER/analysis width=6 format=comma6.0 'MER';
define MID/analysis width=6 format=comma6.0 'MID';
define MON/analysis width=6 format=comma6.0 'MON';
define MOR/analysis width=6 format=comma6.0 'MOR';
define OCE/analysis width=6 format=comma6.0 'OCE';
define PAS/analysis width=6 format=comma6.0 'PAS';
define SAL/analysis width=6 format=comma6.0 'SAL';
define SOM/analysis width=6 format=comma6.0 'SOM';
define SUS/analysis width=6 format=comma6.0 'SUS';
define UNI/analysis width=6 format=comma6.0 'UNI';
define WAR/analysis width=6 format=comma6.0 'WAR';
define TOTAL/computed width=6 format=comma6.0 'TOTAL';

compute TOTAL;
TOTAL = _C2_ + _C3_ + _C4_ + _C5_ + _C6_ + _C7_ + _C8_ + _C9_ + _C10_ + _C11_ + _C12_+ _C13_
+ _C14_ + _C15_ + _C16_ + _C17_ + _C18_ + _C19_ + _C20_ + _C21_ + _C22_;
ENDCOMP;

COMPUTE AFTER;
SPECIALTY = 'TOTAL';
ENDCOMP;

RBREAK AFTER/SUMMARIZE ;

title1 bold h=3 'Managed Care PCPs';
run;
proc report data = AA.CURRSPC1 nocenter nowindows missing spacing=2 split = "@"
style(report)={font_face=times font_size=1 rules=none frame=void borderwidth=0 background=_undef_}
style(header)={font_face=times font_size=1 rules=none frame=void borderwidth=0 background=_undef_ bordercolor=none}
;
column specialty ATL BER BUR CAM CAP CUM ESS GLO HUD HUN MER MID MON MOR OCE PAS SAL
SOM SUS UNI WAR TOTAL;

define specialty/group width = 100 'SPECIALTY';
define ATL/analysis width=6 format=comma6.0 'ATL';
define BER/analysis width=6 format=comma6.0 'BER';
define BUR/analysis width=6 format=comma6.0 'BUR';
define CAM/analysis width=6 format=comma6.0 'CAM';
define CAP/analysis width=6 format=comma6.0 'CAP';
define CUM/analysis width=6 format=comma6.0 'CUM';
define ESS/analysis width=6 format=comma6.0 'ESS';
define GLO/analysis width=6 format=comma6.0 'GLO';
define HUD/analysis width=6 format=comma6.0 'HUD';
define HUN/analysis width=6 format=comma6.0 'HUN';
define MER/analysis width=6 format=comma6.0 'MER';
define MID/analysis width=6 format=comma6.0 'MID';
define MON/analysis width=6 format=comma6.0 'MON';
define MOR/analysis width=6 format=comma6.0 'MOR';
define OCE/analysis width=6 format=comma6.0 'OCE';
define PAS/analysis width=6 format=comma6.0 'PAS';
define SAL/analysis width=6 format=comma6.0 'SAL';
define SOM/analysis width=6 format=comma6.0 'SOM';
define SUS/analysis width=6 format=comma6.0 'SUS';
define UNI/analysis width=6 format=comma6.0 'UNI';
define WAR/analysis width=6 format=comma6.0 'WAR';
define TOTAL/computed width=6 format=comma6.0 'TOTAL';

compute TOTAL;
TOTAL = _C2_ + _C3_ + _C4_ + _C5_ + _C6_ + _C7_ + _C8_ + _C9_ + _C10_ + _C11_ + _C12_+ _C13_
+ _C14_ + _C15_ + _C16_ + _C17_ + _C18_ + _C19_ + _C20_ + _C21_ + _C22_;
ENDCOMP;

COMPUTE AFTER;
SPECIALTY = 'TOTAL';
ENDCOMP;

RBREAK AFTER/SUMMARIZE ;

title1 bold h=3 'Managed Care SPEC/ANC';
run;

ods tagsets.excelxp close;

Re: tagsets.excelxp

Cynthia_sas — Tue, 18 May 2010 04:47:16 GMT

Hi:
When you use TAGSETS.EXCELXP within EG, you -cannot- open the XML file from the EG interface. The reason is that EG wants all XML files to be SASReport XML -- but the TAGSETS.EXCELXP file is NOT SASReport XML. If, however, you go to Windows Explorer and locate the file you just created and right-mouse click to open it -- or open the file from within Excel, then you should not have any problems. These Tech Support notes outline some of the issues that EG has with XML files:
http://support.sas.com/kb/32/924.html
http://support.sas.com/kb/32/133.html

As for your other question, you need to check out some of the sub-options that can be used with TAGSETS.EXCELXP, in particular, the sheet_name sub-option and, I believe, the sheet_interval sub-option. You use the sheet_interval sub-option to control how many tables should be put on one sheet. If you look at the internal documentation, you will see this description:
[pre]
Sheet_Interval: Default Value 'Table'
Values: Table, Page, Bygroup, Proc, None.
This option controls how many tables will go in a worksheet.
In reality only one table is allowed per worksheet. To get more
than one table, the tables are actually combined into one.

Specifying a sheet interval will cause the current worksheet to close.
It is recommended that this always be the first option to insure that
The options following it apply to the new worksheet rather than the
last worksheet.
[/pre]

A good paper that discusses the use of many of the EXCELXP suboptions is here:
http://www.nesug.org/proceedings/nesug08/ap/ap06.pdf (sheet_interval is discussed on page 9)

cynthia

Re: tagsets.excelxp

deleted_user — Tue, 18 May 2010 13:26:15 GMT

My initial intuition was that this issue was EG related, but however as I have mentioned in my post, I also tried this process on 9.1. The only online documentation I found relating to bugs with this process dealt with a templates issue, however I have played around with proc template and other reporting templates and this does not remedy the situation. Perhaps it's an Excel version issue? I have Excel 2003.

Re: tagsets.excelxp

deleted_user — Tue, 18 May 2010 15:14:53 GMT

I finally figured it out:

Apparently you need to run the following code before invoking tagsets.excelxp

http://support.sas.com/rnd/base/ods/odsmarkup/excltags.tpl

SAS needs to do a better job in documenting the need to run this when encountering the issue I described.

Re: tagsets.excelxp

Cynthia_sas — Tue, 18 May 2010 15:37:19 GMT

Sorry...nothing in your original post indicated an ExcelXP version problem to me. I thought you were having the file opening issues with TAGSETS.EXCELXP output files and EG.

The need to update the tagset template for TAGSETS.EXCELXP is mentioned in almost every paper on the subject. It is also the topic of this Tech Support note:
http://support.sas.com/kb/32/394.html

When a sub-option does not work as you expect or as described, then the issue is most likely a version issue.

cynthia