topic Re: Exporting to Excel in SAS Programming

Exporting to Excel

NickAnt — Thu, 13 Mar 2014 12:36:53 GMT

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

Re: Exporting to Excel

RW9 — Thu, 13 Mar 2014 13:30:05 GMT

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;

Re: Exporting to Excel

danbohac — Thu, 13 Mar 2014 13:31:29 GMT

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;

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.

Re: Exporting to Excel

NickAnt — Thu, 13 Mar 2014 13:32:11 GMT

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

Re: Exporting to Excel

RW9 — Thu, 13 Mar 2014 13:40:21 GMT

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.

Re: Exporting to Excel

art297 — Thu, 13 Mar 2014 13:44:36 GMT

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.

Re: Exporting to Excel

RW9 — Thu, 13 Mar 2014 13:46:53 GMT

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;

Re: Exporting to Excel

joedunlavy — Thu, 13 Mar 2014 17:29:08 GMT

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.

Re: Exporting to Excel

Reeza — Thu, 13 Mar 2014 17:46:46 GMT

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;

Re: Exporting to Excel

NickAnt — Thu, 13 Mar 2014 21:02:04 GMT

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?

Re: Exporting to Excel

art297 — Thu, 13 Mar 2014 21:23:32 GMT

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.

Re: Exporting to Excel

art297 — Thu, 13 Mar 2014 21:39:32 GMT

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.

Re: Exporting to Excel

NickAnt — Thu, 13 Mar 2014 22:44:58 GMT

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)

Re: Exporting to Excel

art297 — Thu, 13 Mar 2014 23:12:29 GMT

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

I'll change that on my post as well

Re: Exporting to Excel

NickAnt — Thu, 13 Mar 2014 23:29:49 GMT

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!

Re: Exporting to Excel

art297 — Fri, 14 Mar 2014 00:07:24 GMT

Nick,

I'd like to resolve the matter before I present the macro at SGF in a week and a half. Please contact me, offline tomorrow, at:

atabachneck@gmail.com I didn't understand your last message as you said that you'd use:

c:\nick\Dropbox\test.xlsx

instead of

c:\nick\Dropbox\test.xlsx

They look the same to me. I tested the macro, before posting it, thus would really like to figure out what the problem is.

Art

Re: Exporting to Excel

art297 — Mon, 17 Mar 2014 22:25:19 GMT

For anyone who has been following this thread, the 'need' that Nikolaos had caused us to revise the macro we had written so that it would be useful both as an action command from the SAS Explorer window and as a macro that one can call from either base SAS or EG.

Plus, in the process of accomplishing those modifications, we decided to expand it to include the capabilities of:

1. including a variable name header row than can display either variable names or variable labels

2. including the abilities to either create a new workbook, add a worksheet to an existing workbook

3. copy a table to a range of either a new Workbook, an existing worksheet, or a new worksheet

The published paper, unfortunately, won't reflect the updated code, but the code,powerpoint and updated paper can all be downloaded from:

A Poor/Rich SAS Users Proc Export - sasCommunity

BTW, many thanks to Nokolaos as, while we were trying to help him, he helped us by testing the code.

Art

Re: Exporting to Excel

NickAnt — Mon, 17 Mar 2014 22:38:18 GMT

Glad to help and, of course, to be helped! It is a very good piece of work.

Cheers,

Nick