The SAS Output Delivery System and reporting techniques

ExcelXP tagset not working

Reply
Contributor
Posts: 27

ExcelXP tagset not working

I am a sas user at my company and have been asked to produce some reporting that automatically opens in excel. To accomplish this I have been trying to use the excelXP tagsets (multiple sheets are going to be required for this project).

I have tried many different things and searched a lot of different websites but no matter what I do, excel cannot open the file I create. I get a window message 'Problems during load...Style'.

Here is some sample code that I run that will produce this error:

data whole_v2;
set sashelp.prdsale;
where quarter = 1;
terr_id = substr(division,1,3);
productgroup = substr(product,1,1);
total_scripts = actual / 100;
bsm = region;
physician_name = substr(compress(country,'.'),1,2);run;

ODS TAGSETS.EXCELXP FILE=ODSFILE style=sasweb;

proc report data=whole_v2 nowd MISSING split='/';
COLUMN terr_id bsm physician_name
total_scripts, year, productgroup;
define terr_id/group 'Terr/ID';
define bsm/group;
DEFINE physician_name / group 'Phy/Name';
DEFINE year / across ' ';
DEFINE productgroup / across ' ';
DEFINE total_scripts / sum format=comma12. ' ';
break after terr_id/summarize
style=[font_weight=bold background=red];run;
ODS TAGSETS.EXCELXP CLOSE;

I found this code off of the forum as an example someone got to work. I am running SAS 9 (TS1M3). I assume this is 9.1.3 and Excel 2007. When I first used tagset excelxp I got a warning message of "Experimental Excel XP tagset." I could not find any information as to what version it was. Maybe somebody can tell based on the warning message I received.

I then went to http://support.sas.com/rnd/base/ods/odsmarkup/index.html to get the update. I ran the same code but with this updated tagset. I got the same error message.
Super Contributor
Posts: 359

Re: ExcelXP tagset not working

Did you load the tagset in the same job? The system I am working on now will not allow me to load the tagset permanently, so I have to reload it each time.
Contributor
Posts: 27

Re: ExcelXP tagset not working

I don't know if this matters but I am working on a mainframe. I put the updated (Version 1.8.6) into a dataset 'EXCELXP.TPL'. I then use a %include to call the template into my job. Here is some text from my log:

"
2 %INCLUDE'EXCELXP.TPL';
NOTE: TAGSET 'Tagsets.ExcelBase' has been saved to: SASUSER.TEMPLAT
WARNING: The quoted string currently being processed has become more than 512 characters long. You may have unbalanced quotation marks.
"

I'm not sure what to make of the warning.

I then used ODS PATH SHOW to make sure I was reading the template from the right location, Here is the log from that:

"
Current ODS PATH list is:

1. SASUSER.TEMPLAT(UPDATE)
2. SASHELP.TMPLMST(READ)
"
I feel that I am loading it correctly. Also I noticed that when I load the updated tagset I no longer get the warning that excelxp is an experimental tagset. I do get the following message in my log:

"
NOTE: Writing TAGSETS.EXCELXP Body file: ODSFILE
NOTE: This is the Excel XP tagset (SAS 9.1.3, v1.86, 04/15/08).
"
Contributor
Posts: 27

Re: ExcelXP tagset not working

Here is the typical error message I get when trying to open the file in excel:

XML PARSE ERROR: Null or missing attribute value
Error occurs at or below this element stack:


Super Contributor
Super Contributor
Posts: 3,174

Re: ExcelXP tagset not working

You have a serious, though subtle, problem with the "uploaded" file. I expect you will want to verify the LRECL= used when the mainframe file was created. It may be that you have coding that goes beyond the LRECL= causing an issue. Or, less likely, it could be a translation problem, depending on how you loaded the updated tagset content from the SAS.COM support site (eventually) making it to the mainframe file you are using.

To debug this situation, transfer a copy of the mainframe file to your PC and then compare the two for differences.

Scott Barry
SBBWorks, Inc.
Contributor
Posts: 27

Re: ExcelXP tagset not working

Thanks for the suggestion. I will do that.
Super Contributor
Super Contributor
Posts: 3,174

Re: ExcelXP tagset not working

Here you go -- the max length of a code line is 179 however do consider that a future update may change. You will want to set your mainframe file LRECL= parameter to be at least the max value or higher.

SAS-distributed AUTOCALL macro and this type of supplemental code does not appear to follow a coding standard.

Scott Barry
SBBWorks, Inc.

6 filename indata "c:\data\sas_LocalCode\excltags_tpl.txt";
7 data _null_;
8 infile indata;
9 input ;
10 run;

NOTE: The infile INDATA is:
File Name=c:\data\sas_LocalCode\excltags_tpl.txt,
RECFM=V,LRECL=256

NOTE: 7459 records were read from the infile INDATA.
The minimum record length was 0.
The maximum record length was 179.
NOTE: DATA statement used (Total process time):
real time 0.04 seconds
cpu time 0.00 seconds
Contributor
Posts: 27

Re: ExcelXP tagset not working

I'm going to continue going down this path that the problem is in my 'uploaded' excelxp file. Before I uploaded the file I allocated the datset. Here are the parameter in case someone can see an obvious problem.

Data Set Name . . . . :EXCELXP.TPL

General Data Current Allocation
Management class . . : STANDARD Allocated cylinders : 10
Storage class . . . : PRIMARY Allocated extents . : 5
Volume serial . . . : CPY002
Device type . . . . : 3390
Data class . . . . . : **None** Current Utilization
Organization . . . : PS Used cylinders . . : 10
Record format . . . : FB Used extents . . . : 5
Record length . . . : 1000
Block size . . . . : 27000
1st extent cylinders: 2
Secondary cylinders : 2
Data set name type : SMS Compressible : NO

Creation date . . . : 2010/03/04 Referenced date . . : 2010/03/04
Expiration date . . : ***None***
Contributor
Posts: 27

Re: ExcelXP tagset not working

Well, I ftp'd my dataset 'EXCELXP.TPL' to a text file and used winmerge to compare it to the text file from the sas website for v1.86. Win merge found 18 lines that were different. All 18 lines were different due to spaces in the file from the SAS website (almost always on a blank line). I went through it three times with two different people and the only differences seem to be the extra spaces. I Don't think the spacing would be causing my problem. I'm not sure where to go from here.

p.s. I tried loading and using v1.72 but I got the same results when trying to open the excel file.
SAS Super FREQ
Posts: 8,868

Re: ExcelXP tagset not working

Hi:
I tried your code, as posted and it worked for me. I suspect you need to update your copy of the ExcelXP tagset. When I run it in SAS 9.2, this is the note I get in the log:
[pre]
2422 ODS TAGSETS.EXCELXP FILE='c:\temp\tryreport.xls' style=sasweb;
NOTE: Writing TAGSETS.EXCELXP Body file: c:\temp\tryreport.xls
NOTE: This is the Excel XP tagset (SAS 9.1.3, v1.75, 07/26/07). Add options(doc='help') to the
ods statement for more information.
[/pre]

For more information on how to update TAGSETS.EXCELXP so you are running the most current version of the tagset, refer to this Tech Support note:
http://support.sas.com/kb/32/394.html

cynthia
Contributor
Posts: 27

Re: ExcelXP tagset not working

Posted in reply to Cynthia_sas
Cynthia, I noticed that you are using v1.75. I thought I would try to execute my program but with this earlier version. When I go to the website http://support.sas.com/rnd/base/ods/odsmarkup/index.html, that version is not available. It goes from v1.72 to v1.86. Is there a way to get v1.75?
SAS Super FREQ
Posts: 8,868

Re: ExcelXP tagset not working

Hi:
Version 1.75 is the version that comes with SAS 9.2. In my experience either version 1.72 or version 1.86 should work for you.

However, there was a previous posting about updating TAGSETS.EXCELXP on the mainframe -- and as I remember, the upshot was that there's some encoding issue and I think you have to contact Tech Support directly for a version of the tagset template already in binary form for the mainframe.

This is the previous posting about it.
http://support.sas.com/forums/thread.jspa?messageID=8004ὄ

I think if you contact Tech Support and mention the above thread, you will be able to get a version of the tagset to use on the mainframe without going through everything that was described in this post (8004).

cynthia
Super Contributor
Super Contributor
Posts: 3,174

Re: ExcelXP tagset not working

Posted in reply to Cynthia_sas
Also, there are two OPTIONS settings S= and S2= which may have relevance - read up on them in the DOC or at the SAS support http://support.sas.com/ website.

I suspect that if you increased the amount of SAS log diagnostic output, it may also help debug the warning, such as adding:

OPTIONS SOURCE SOURCE2 MGEN SGEN MPRINT;

Scott Barry
SBBWorks, Inc.
Contributor
Posts: 27

Re: ExcelXP tagset not working

Thanks for everyone's help on this. I worked some with our installation person but to no luck. I have contacted support via online. Hopefully they are able to provide me with the 'mainframe' verson of the updated tagsets.
Super Contributor
Super Contributor
Posts: 3,174

Re: ExcelXP tagset not working

Based on my personal experience, your file upload method (whether FTP or TN3270-related IND$FILE) could have an impact on the resulting mainframe file -- such as translated characters like the vertical-bar and logical-not sign.

For years with SAS coding, I always use !! for string-concatenations rather than || -- and whenever possible I avoid special characters like ^ (hat-sign or logical-not) and use mnemonic-equivalent operators.

Have a great weekend, people!

Scott Barry
SBBWorks, Inc.
Ask a Question
Discussion stats
  • 17 replies
  • 1295 views
  • 0 likes
  • 4 in conversation