The SAS Output Delivery System and reporting techniques

ODS Excel Multiple Sheets

Reply
New Contributor
Posts: 3

ODS Excel Multiple Sheets

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!

 

Super User
Posts: 3,252

Re: ODS Excel Multiple Sheets

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.

New Contributor
Posts: 3

Re: ODS Excel Multiple Sheets

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

SAS Super FREQ
Posts: 8,864

Re: ODS Excel Multiple Sheets

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

New Contributor
Posts: 3

Re: ODS Excel Multiple Sheets

Posted in reply to Cynthia_sas

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

SAS Super FREQ
Posts: 8,864

Re: ODS Excel Multiple Sheets

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
Ask a Question
Discussion stats
  • 5 replies
  • 861 views
  • 0 likes
  • 3 in conversation