Exporting to Excel

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

Exporting to Excel

Hello,

Is there a way to export multiple tables in one excel spreadsheet? For instance, I run 10 regressions and I would like to put all 10 R-Square table in the same sheet.

Thank you,

Nick


Accepted Solutions
Solution
‎03-13-2014 09:44 AM
PROC Star
Posts: 7,364

Re: Exporting to Excel

Nick,

If you are on Windows the answer is yes!  Download the macro from the page at:

http://www.sascommunity.org/wiki/A_Poor/Rich_SAS_Users_Proc_Export

The paper describes implementing the macro as a menu item in your SAS explorer window but, for what you want to do, you could just run the macro by itself.

You would either have to use the same filename for all 10 output datasets or you would have to modify the macro, slightly, to specify a single output file.

If the workbook doesn't exist yet, you would create it by calling the macro as follows: %exportxl(libname, filename,S,Y,N)

where you would replace libname and filename with your libname and your filename.  The macro, in turn, would create a workbook in the path specified by the libname, and with a filename that matches your filename (but with an xlsx extension), and a worksheet with the same name as your filename, beginning in cell A1, and starting with a row containing variable names.

Then, for the other nine tables, you would call the macro as follows: %exportxl(libname, filename,A,Y,Y)

where you would replace libname and filename with your libname and your filename.  The macro, in turn, would open the workbook in the path specified by the libname, and with a filename that matches your filename (but with an xlsx extension) and, in the worksheet with the same name as your filename, beginning in cell that you would specify when prompted, and starting with a row containing variable names.

The macro was written by Tom Abernathy, FriedEgg and myself and is going to be presented at the upcoming SGF on Wednesday, March 26th, 2014.

View solution in original post


All Replies
Super User
Super User
Posts: 7,417

Re: Exporting to Excel

Easiest way is ods tagset:

ods tagsets.excelxp file=xxx;

then before each proc

ods tagsets.excelxp options(sheet_name="yyyy");

then at end

ods tagset.excelxp close;

Occasional Contributor
Posts: 8

Re: Exporting to Excel

Thank you RW9, but does this put multiple outputs in the same sheet?

Super User
Super User
Posts: 7,417

Re: Exporting to Excel

Then you would just have:

ods tagsets.excelxp file=xxx;

ods tagsets.excelxp options(sheet_name="yyyy");

proc one

proc two

...

ods tagset.excelxp close;

Note I will actually test this later but in theory should work.

Super User
Super User
Posts: 7,417

Re: Exporting to Excel

To add to my post, you need to have the option sheet_interval='none'

ods tagsets.excelxp file=xxx;

ods tagsets.excelxp options(sheet_name="yyyy" sheet_interval='none');

proc one

proc two

...

ods tagset.excelxp close;

Super User
Posts: 17,912

Re: Exporting to Excel

Combined with ODS Select you can get closer to your requirements. Though if you want to customize the tables then there are better ways Smiley Happy

ods tagsets.excelxp file="C:\_localdata\sample.xml" sytle=journal options(embedded_titles='yes' sheet_interval='none' sheet_name="Example");

title 'Class Model';

ods select FitStatistics;

proc reg data=sashelp.class;

model weight=height age;

run;quit;

title 'Car Model';

ods select FitStatistics;

proc reg data=sashelp.cars;

model mpg_city=weight enginesize cylinders horsepower;

run;quit;

ods tagsets.excelxp close;

New Contributor
Posts: 4

Re: Exporting to Excel

You can run a static number of export statements:

PROC EXPORT DATA=dataset1
OUTFILE= "&path\filename.xls"
DBMS=EXCEL REPLACE;
LABEL;
SHEET="tab_name1";
RUN;

PROC EXPORT DATA=dataset1
OUTFILE= "&path\filename.xls"
DBMS=EXCEL REPLACE;
LABEL;
SHEET="tab_name1";
RUN;

Alternatively, there other methods that use macros and tagset to produce multiple sheets just from one dataset.

https://support.sas.com/resources/papers/proceedings12/150-2012.pdf

http://www.sascommunity.org/wiki/Automatically_Separating_Data_into_Excel_Sheets

Regards,

Dan.

Solution
‎03-13-2014 09:44 AM
PROC Star
Posts: 7,364

Re: Exporting to Excel

Nick,

If you are on Windows the answer is yes!  Download the macro from the page at:

http://www.sascommunity.org/wiki/A_Poor/Rich_SAS_Users_Proc_Export

The paper describes implementing the macro as a menu item in your SAS explorer window but, for what you want to do, you could just run the macro by itself.

You would either have to use the same filename for all 10 output datasets or you would have to modify the macro, slightly, to specify a single output file.

If the workbook doesn't exist yet, you would create it by calling the macro as follows: %exportxl(libname, filename,S,Y,N)

where you would replace libname and filename with your libname and your filename.  The macro, in turn, would create a workbook in the path specified by the libname, and with a filename that matches your filename (but with an xlsx extension), and a worksheet with the same name as your filename, beginning in cell A1, and starting with a row containing variable names.

Then, for the other nine tables, you would call the macro as follows: %exportxl(libname, filename,A,Y,Y)

where you would replace libname and filename with your libname and your filename.  The macro, in turn, would open the workbook in the path specified by the libname, and with a filename that matches your filename (but with an xlsx extension) and, in the worksheet with the same name as your filename, beginning in cell that you would specify when prompted, and starting with a row containing variable names.

The macro was written by Tom Abernathy, FriedEgg and myself and is going to be presented at the upcoming SGF on Wednesday, March 26th, 2014.

Occasional Contributor
Posts: 8

Re: Exporting to Excel

Hi Arthur,

Thank you for the answer! I follow you directions very precisely, but every time I run the macro the second time, SAS crushes.

Have you experienced that? What can I do to remedy it?

PROC Star
Posts: 7,364

Re: Exporting to Excel

My initial guess is that there is a problem in how you called the macro.  However, while we never thought of making the macro available as a stand alone macro, we never thought the problem through.  This afternoon I did make a stand alone version.  Give me a couple of minutes and I 'll provide a link to the stand alone macro and instructions on how to call it.

N/A
Posts: 1

Re: Exporting to Excel

Throwing it out as a suggestion.  If the 10 tables have the same variables, can you just set them on top of one another?

data want;

     set have1 have2 have3 .. have10;

run;

You can maybe add a variable that distinguishes the different tables in the list. 

Then just proc export 'want' into a single excel sheet.

PROC Star
Posts: 7,364

Re: Exporting to Excel

The new stand alone version of the macro can be found at:

http://www.sascommunity.org/mwiki/images/c/c9/1793-2014a.sas

The macro has 7 named parameters, 5 of which have default values thus don't need to be included unless you want to change their values.  The macro declaration reads as follows:

%macro exportxlsx(data=, outfile=, sheet=Sheet1, type=S, usenames=YES, range=A1, replace=YES);

In the following example, I first create a workbook called c:\art\classtest, with a worksheet called test and containing a copy of sashelp.class, with the first row containing variable names, and rows 2 thru 20 containing the data.

In the example's second call of the macro, I add another copy of sashelp.class, again in sheet test, but this time starting in cell a22.

%exportxlsx(data=sashelp.class,

  outfile=c:\art\classtest.xlsx,

  type=S,

  sheet=test)

%exportxlsx(data=sashelp.class,

  outfile=c:\art\classtest.xlsx,

  type=A,

  sheet=test,

  range=a22)

Let me/us know if that works for you.

Occasional Contributor
Posts: 8

Re: Exporting to Excel

Arthur, I run the macro as instructed and SAS crushes with the first try, which is the following:

%exportxlsx(data=work.test5,

  outfile=c:\nick\Dropbox\test,

  type=S,

  sheet=testa)

PROC Star
Posts: 7,364

Re: Exporting to Excel

my fault.  list the outfile as c:\nick\Dropbox\test.xlsx,

I'll change that on my post as well

Occasional Contributor
Posts: 8

Re: Exporting to Excel

Arthur, it still crushes SAS. I will try tomorrow using "c:\nick\Dropbox\test.xlsx"  instead of c:\nick\Dropbox\test.xlsx and let you know how it goes. Thank you for your time and effort!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 17 replies
  • 577 views
  • 7 likes
  • 6 in conversation