topic Re: Having issue with creating excel file with multiple sheet in SAS Procedures

Having issue with creating excel file with multiple sheet

Aman4SAS — Tue, 31 Mar 2015 04:50:51 GMT

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

Re: Having issue with creating excel file with multiple sheet

SASKiwi — Tue, 31 Mar 2015 09:27:29 GMT

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 maintenance release of SAS® 9.3 (TS1M1)

Re: Having issue with creating excel file with multiple sheet

Steelers_In_DC — Tue, 31 Mar 2015 16:47:46 GMT

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;