BookmarkSubscribeRSS Feed
supp
Pyrite | Level 9
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.
17 REPLIES 17
Flip
Fluorite | Level 6
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.
supp
Pyrite | Level 9
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).
"
supp
Pyrite | Level 9
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:


sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
supp
Pyrite | Level 9
Thanks for the suggestion. I will do that.
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
supp
Pyrite | Level 9
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***
supp
Pyrite | Level 9
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.
Cynthia_sas
SAS Super FREQ
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
supp
Pyrite | Level 9
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?
Cynthia_sas
SAS Super FREQ
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
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
supp
Pyrite | Level 9
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.
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.

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
  • 17 replies
  • 3518 views
  • 0 likes
  • 4 in conversation