CODEWITHSUNDEEP
excelsas
Beta
Beta

Reputation: 1736

Export to Excel using SAS

Suppose I have 2 SAS dataset: test1.sas & Test2.sas. Now I want to export these 2 dataset into excel, where in the excel file Sheet1 will have test1.sas data & in Sheet2 will have test2.sas data.

How to do it?

Upvotes: 2

Views: 2027

Answers (5)

Drew
Drew

Reputation: 21

If you need to format the output without touching the Excel document (I worked in a banking department where outputs were read only) you can use XML to do this .

Upvotes: 1

Yarrney
Yarrney

Reputation: 11

I'd say the easiest way to export to excel (depending on your version of SAS) is to use PROC EXPORT and specify excel as your dbms. You can specify the same file to be updated but a different tab for each proc export you call.

So as an example with two datasets on two separate tabs: 

proc export data=test1 dbms=excel
replace outfile ="C:\Your_file_path\your_file.xls";
sheet = sheet1;
run;

proc export data=test2 dbms=excel
replace outfile ="C:\Your_file_path\your_file.xls";
sheet = sheet2;
run;

Upvotes: 1

user667489
user667489

Reputation: 9569

The ODS methods in @CarolinaJay65's answer are very flexible, but they might be overkill if you just want a raw export.

A simpler option, if it's available in your SAS installation, is to use the excel libname engine. This allows you to use an excel workbook as a sas library, with each dataset in the library occupying one sheet in the workbook.

http://www.wuss.org/proceedings09/09WUSSProceedings/papers/app/APP-Benjamin.pdf

If you also want to automate lots of formatting or generate further output (e.g. charts), an alternative to using ODS is to create a VBA macro, save it in a template workbook, and have SAS call that macro to run on your output via a DDE command:

http://www.lexjansen.com/pharmasug/2005/coderscorner/cc21.pdf

This allows you to use excel's macro recorder to capture the actions you want to automate, rather than having to work out how to produce the equivalent output via ODS / DDE.

Upvotes: 2

Robert Penridge
Robert Penridge

Reputation: 8513

If it's not something you want to automate, and you are using the Base SAS IDE, you can simply right-click on the dataset in the SAS Explorer window and select View in Excel.

Upvotes: 1

Jay Corbett
Jay Corbett

Reputation: 28391

Start with this paper. This and this are also good references.

Using ODS, you can output your data using reporting procs (Ex. Proc PRINT and REPORT) to XML. Not only can you create multisheet output, but you can format dates, set autofilters and place headers.

Upvotes: 4

Related Questions