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!
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.
Hi SASkiwi,
I don't believe what you said is the case. I'm remediating code from:
ods tagsets.excelxp
to
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
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
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.