BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
NickAnt
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

17 REPLIES 17
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

NickAnt
Fluorite | Level 6

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

Reeza
Super User

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;

danbohac
Calcite | Level 5

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.

art297
Opal | Level 21

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.

NickAnt
Fluorite | Level 6

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?

art297
Opal | Level 21

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.

joedunlavy
Calcite | Level 5

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.

art297
Opal | Level 21

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.

NickAnt
Fluorite | Level 6

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)

art297
Opal | Level 21

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

I'll change that on my post as well

NickAnt
Fluorite | Level 6

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!

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
  • 17 replies
  • 1569 views
  • 7 likes
  • 6 in conversation