topic Re: tagsets.ExcelXP - Suppressing metadata sheets in ODS and Base Reporting

tagsets.ExcelXP - Suppressing metadata sheets

deleted_user — Thu, 30 Sep 2010 14:30:26 GMT

Hi,

I'm producing output in Excel using tagsets.ExcelXP and for some reason I'm getting 2 additional (unwanted) sheets created containing metadata. The first holds the following:

Directory
Libref
Engine
Physical Name
File Name
Inode Number
Access Permission
Owner Name
File Size (bytes)

and the second is a listing of Name, Member Type, File Size and Last Modified.

I've found that the issue occurs when the code is run from a %include within a program run from batch, but what's also strange is that this is not happening for all files output this way, yet the code used is the same:

ods listing close;
ods tagsets.ExcelXP file = "listing.xls";
** Open Sheet in Excle File **;
ods tagsets.ExcelXP options(embedded_titles='yes'
embedded_footnotes='yes'
sheet_name="CBRM_1_"
default_column_width= "10, 10"
FROZEN_HEADERS='4');

** Proc Report **;
proc report data=reportdata nowd split="#" missing
style(report)=[rules=all cellspacing=0 font_size=8pt font_face=arial]
style(header)=[background=lightskyblue foreground=black just=left font_size=8pt font_weight=bold]
style(column)=[background=white foreground=black vjust=top just=left font_size=8pt];

column x y z;

define x / order noprint;
define y / display "Y" width=9 left;
define z / display "Z" width=11 left;

run;

ods tagset.ExcelXP close;
ods listing;

It does not seem to happen when the code is run interactively, nor when the individual programs are run in batch. Only when they're run as %includes within the main program.

Does anyone have any idea what may be causing this and what I can do to prevent the additional sheets being produced?

Thanks!

Re: tagsets.ExcelXP - Suppressing metadata sheets

Cynthia_sas — Thu, 30 Sep 2010 15:29:01 GMT

Hi:
The information on the unwanted sheet looks suspiciously like the PROC DATASETS output shown in this documentation topic:
http://support.sas.com/documentation/cdl/en/basess/58133/HTML/default/a001397000.htm

I would suspect that either your %INCLUDE has a PROC DATASETS step that does not end with QUIT; or you have previously run an unclosed PROC DATASETS in your session. PROC DATASETS does not end with RUN; -- it ends with QUIT; so it is possible that in some instances (such as local submission, you do NOT have PROC DATASETS running before you do your submit.) But in other instances, like the %INCLUDE, you might have a PROC DATASETS somewhere in the code that precedes the included code???

Are you sure that the %INCLUDE code is -EXACTLY- the same as the code when you submit locally???? Can you post a sample of your file WORK.REPORTDATA?? Or, can you duplicate the results using SASHELP datasets????

You say that "this is not happening for all files output this way" -- but your program is always going against WORK.REPORTDATA, which has the variables X, Y and Z. What "other" files??? How are "all the files" files run through PROC REPORT??? Or, by "all the files" do you mean the LISTING.XLS output from ODS TAGSETS.EXCELXP.

Do you see any difference in the LOG messages when you submit the code locally versus submitting it with %INCLUDE???? If there are differences, can you post the LOGs here??? Please check the log carefully after every execution and look for "file in use" messages such as this one:
[pre]
2963 ods tagsets.excelxp file='c:\temp\listing.xls' style=sasweb;
NOTE: Writing TAGSETS.EXCELXP Body file: c:\temp\listing.xls
ERROR: File is in use, c:\temp\listing.xls.
WARNING: No body file. TAGSETS.EXCELXP output will not be created.
2964 proc print data=sashelp.class;
2965 run;

WARNING: No output destinations active.
[/pre]

I don't have any other more concrete suggestions. If you can't figure it out, you might want to open a track with Tech Support -- they can look at ALL your code -- including the code that might precede the %INCLUDE code to see if they can figure out what's happening.

cynthia

Re: tagsets.ExcelXP - Suppressing metadata sheets

deleted_user — Fri, 01 Oct 2010 09:03:26 GMT

Hi Cynthia,

Sorry I didn't provide all the information you may have needed. I tried to keep it simple so as not to bog the problem down with too many details.

Anyway thanks for your reply. I have been through the various programs and macros called throughout the whole process and did indeed find a few PROC DATASETS' which ended with RUN; instead of QUIT;. However this still did not fix the problem. I then compared the interactive log to the batch run and found that one of the DATASETS procedures before the output was putting a list to the log in batch. I added a NOLIST option to it and thankfully that did the job!

Thanks very much for your help! I never would have thought to check anything like that!

Kind regards,
Adam.