BookmarkSubscribeRSS Feed
tegant
Calcite | Level 5

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!

 

5 REPLIES 5
SASKiwi
PROC Star

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.

tegant
Calcite | Level 5

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

Cynthia_sas
SAS Super FREQ

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

tegant
Calcite | Level 5

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

Cynthia_sas
SAS Super FREQ
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

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
  • 5 replies
  • 3988 views
  • 0 likes
  • 3 in conversation