topic Re: ODS Excel Multiple Sheets in ODS and Base Reporting

ODS Excel Multiple Sheets

tegant — Thu, 15 Oct 2015 18:25:29 GMT

Hi,

I'm having trouble getting ODS Excel to output a .xlsx file in the format I'd like. I have the code:

%MACRO print (cat=, colcat=, subcat=, blank=);

ods excel file= "&outpt.&filedate Target All Bed Day Variance.xlsx";

ods excel options(sheet_name= "&subcat &colcat" sheet_interval='none' absolute_column_width = "8,9,7,7,6,6,9,7,7,6,6,9,7,7,6,6,9,7,7,6");

/* lots of unshown, not relevant to question code here */

run;

ods excel close;

%MEND

print;

%print (cat=PCSA, colcat=PCSA, subcat=NORTH, blank=mob);

%print (cat=PCSA, colcat=PCSA, subcat=EAST, blank=mob);

Because I'm restating the file location within the macro, the file is overwritten each time I call the macro, and instead of getting a .xlsx file with two sheets (one for each time the macro is called), I only get the sheet created for the last time the macro is called.

If I remove the file path outside (above) the macro, I get the error:

ERROR: Insufficient authorization to access /apps/sas/sas94/config/Lev1/NWWest/sasexcl.xlsx.

The macro then doesn't know where to look for the file. Any suggestions?

Thank you!

Re: ODS Excel Multiple Sheets

SASKiwi — Thu, 15 Oct 2015 18:38:39 GMT

With ODS you cannot add to an existing Excel workbook, you can only create one from scratch each time.

If you want to add to an existing Excel workbook you need to look an other export options like PROC EXPORT or the EXCEL LIBNAME engine.

Re: ODS Excel Multiple Sheets

tegant — Thu, 15 Oct 2015 18:48:37 GMT

Hi SASkiwi,

I don't believe what you said is the case. I'm remediating code from:

ods tagsets.excelxp

ods excel

The code works in ods tagsets.excelxp when the filename above the macro. It produces a .xls workbook with multiple tabs. I need a .xlsx workbook, so I need to use 'ods excel'.

Thanks,

Tom

Re: ODS Excel Multiple Sheets

Cynthia_sas — Thu, 15 Oct 2015 21:57:54 GMT

Hi:
SAS Kiwi is correct, with a FILE= option, the file is completely overwritten each time. Generally, you want to do something like this:

ods excel file='something.xlsx';

%macro call for first sheet;
%macro callfor second sheet;

ods excel close;

then INSIDE the macro definition if you want to provide a sheet name or other suboptions, your macro does NOT provide file=, it only provides the sheet name and suboptions:

%macro examp;
ods excel options(sheet_name="&macvar" ...other options);
proc whatever data=work.somefile;

run;
%mend examp;

WITHOUT any FILE= or CLOSE inside the macro code.

ODS has ALWAYS worked this way. EVERY FILE= overwrites an existing file of the same name. An ODS FILE= will NOT add to an existing file, unless you are 1) using HTML and 2) using a FILEREF with the MOD option. An ODS TAGSETS.EXCELXP or ODS EXCEL FILE= will NOT add to an Excel workbook.

cynthia

Re: ODS Excel Multiple Sheets

tegant — Thu, 15 Oct 2015 23:58:30 GMT

Thanks Cynthia...while what you wrote works for me using 'ods tagsets.excelxp' to create a multisheet .xls file, it does not work for me when I use 'ods excel' to create a multisheet .xlsx file. I get this error:

ERROR: Insufficient authorization to access /apps/sas/sas94/config/Lev1/NWWest/sasexcl.xlsx.

It's not finding the original file and looking somewhere else where the file doesn't exist and where I don't have access to.

Any ideas?

Thanks,

Tom

Re: ODS Excel Multiple Sheets

Cynthia_sas — Fri, 16 Oct 2015 02:40:28 GMT

Hi: Unfortunately, this is an instance where someone has to look at ALL your code, including the long parts and the macro code. It seems like you have an access problem or an issue with your macro variable or you have a stray CLOSE someplace in your code that did not get deleted.

This is really something that is best worked on with Tech Support. They can replicate your version of SAS and experiment with your code to see if they experience the same issue that you report.

cynthia