topic Re: Macro output multiple data to multiple tabs into one excel. in SAS Programming

Macro output multiple data to multiple tabs into one excel.

jiangmi — Thu, 12 Feb 2015 22:45:08 GMT

Hi, All,

Is there a way that we can append results from macro runs to multiple tabs in one single excel file?

Take an example like below, each time the macro run, new output will replace the old. However, I want both results to be present in one excel file (picture show).

I also tried ODS TAGSETS, it doesn't seem to work either. Please offer opinions. Thanks. Joe

data have1;

sex='M';run;

data have2;

sex='F';run;

%macro test (data, var);

proc export

data=&data (where=(sex="&var"))

outfile="/SASData/SASUSER/Range/why2.xlsx"

dbms=xlsx replace;

sheet="&var";

run;

%mend test;

%test(have1, M)

%test(have2, F)

Re: Macro output multiple data to multiple tabs into one excel.

Reeza — Thu, 12 Feb 2015 23:03:55 GMT

Try DBMS=ExcelCS

You can do it using XLSX if you have SAS 9.4 or applied a hot fix to SAS 9.3

Re: Macro output multiple data to multiple tabs into one excel.

jiangmi — Fri, 13 Feb 2015 02:46:03 GMT

What kind of hotfix?

Re: Macro output multiple data to multiple tabs into one excel.

Reeza — Fri, 13 Feb 2015 05:42:35 GMT

Hot Fixes for SAS/ACCESS Interface to PC Files 9.3_M1

The XLSX engine is enhanced to write multiple sheets per Microsoft Excel file in the first maintenance release of SAS® 9.3 (TS1M1)

I don't know if you have 9.3TS1M2 if this would be fixed or not.

At any rate, tagsets.excelxp will definitely work, but it's a different structure for the code.

Untested, but it would be something like the following:

data have1;

sex='M';run;

data have2;

sex='F';run;

%macro test (data, var);

options tagsets.excelxp(sheet_name="&var");

proc print data=&data (where=(sex="&var"));

run;

%mend test;

ods tagsets.excelxp file="C:\temp\out.xlsx" style=journal;

%test(have1, M)

%test(have2, F)

ods tagsets.excelxp close;

Re: Macro output multiple data to multiple tabs into one excel.

jiangmi — Fri, 13 Feb 2015 15:31:10 GMT

Thanks, Reeza,

I actually read your old post on the same issue:

I am running SAS EG on server, I don't have install privilege either.

The ' DBMS=ExcelCS' doesn't seem to work.

Putting macros in ods tagsets isn't promising either, after copying pasting the code and several modification tries.

It may has something to do with my connection to the server, cause I got error msg like 'ERROR: Server Name is invalid or missing.'. I will surely try them on my local SAS later today.

Thanks again.