Help using Base SAS procedures

Having issue with creating excel file with multiple sheet

Reply
Super Contributor
Posts: 266

Having issue with creating excel file with multiple sheet

Hi ,

i am facing problem to create excel file with multisheet in my new PC. using sas 9.3 - 64 bit

using code is below:

%macro test(nm);
99   proc export data=dw.sas_report_brand_map(where =(index(upcase(REPORT_BRAND_DESCR),"&nm")>0))
100  outfile="Z:\SAS Support\brands.xlsx" dbms=xlsx ;
101  sheet="&nm";
102  run;
103  %mend;
104  %test(xyz);

NOTE: The export data set has 32 observations and 13 variables.
NOTE: "Z:\SAS Support\brands.xlsx" file was successfully created.
NOTE: PROCEDURE EXPORT used (Total process time):
      real time           0.14 seconds
      cpu time            0.03 seconds


105  %test(abc);

NOTE: Export cancelled.  Output file Z:\SAS Support\brands.xlsx already exists. Specify REPLACE
      option to overwrite it.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE EXPORT used (Total process time):
      real time           0.06 seconds
      cpu time            0.01 seconds


i have used with or without replace option, result is differing only that with replace ,
sas creating file with second call macro file. its completly replace previous one.

can any one please suggest me how to resolve this issue.

Thanks

Super User
Posts: 3,256

Re: Having issue with creating excel file with multiple sheet

What maintenance release in version 9.3 (see the top of your SAS log)? Should be OK from M1 onwards:

51580 - The XLSX engine is enhanced to write multiple sheets per Microsoft Excel file in the first m...

Valued Guide
Posts: 860

Re: Having issue with creating excel file with multiple sheet

Here is a template that should work for you:

ods _all_ close;

ods tagsets.ExcelXP path='PATH' file='OUTPUT.xml'

style=printer;

ods tagsets.ExcelXP options(sheet_name='SHEET NAME ONE' AUTOFILTER = 'ALL');

proc print data=FILE1 noobs;

var _all_;run;quit;

ods tagsets.ExcelXP options(sheet_name='SHEET NAME TWO' AUTOFILTER = 'ALL');

proc print data=FILE2 noobs;

var _all_;run;quit;

ods tagsets.ExcelXP close;

Ask a Question
Discussion stats
  • 2 replies
  • 181 views
  • 0 likes
  • 3 in conversation