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
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.
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;
Thank you RW9, but does this put multiple outputs in the same sheet?
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.
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;
Combined with ODS Select you can get closer to your requirements. Though if you want to customize the tables then there are better ways
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;
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.
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.
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?
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.
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.
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.
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)
my fault. list the outfile as c:\nick\Dropbox\test.xlsx,
I'll change that on my post as well
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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.