Export data from SAS into Microsoft Excel

There are a number of ways to export data from SAS into Microsoft Excel. However, very few allow for exporting into custom-formatted spreadsheets such as those demanded for specific reports, in which explicit attributes such as font sizes and column widths are required. Traditionally, Dynamic Data Exchange (DDE) was the only way to do this. Now there are newer methods for this which are considered superior…

Various methods are —

1. Proc Export / SAS Export wizard
2. Dynamic Data Exchange (DDE)
3. ODS CSV / HTML xls file route…create a .csv file and open with Excel
4. SAS to Excel via XML
5. Libname with xls
6. Using Display manager command Dexport to create a csv file

1. Proc Export / SAS Export wizard

2. Dynamic Data Exchange (DDE)

Export SAS datasets to Excel with DDE – 

3. ODS CSV / HTML xls file route…create a .csv file and open with Excel

4. SAS to Excel via XML

Export SAS datasets to Excel via XML –

5. Libname with xls

Export SAS datasets to Excel -

6. Using Display manager command Dexport to create a csv file….The Display manaager (DM Dexport) command invokes the External File Interface of SAS…

dm "dexport sashelp.class 'C:\class.csv' ";

dm "dexport sashelp.class 'C:\class.xls' ";

Interesting References….

Excellent Ways of Exporting SAS Data to Excel

SAS Macros to Read data from MS Excel using DDE

Here are a set of SAS Macros to read data from MS Excel using DDE…

Let me explain with some scenarios with the Excel data stored in “C:\Names.xlsx” in worksheet named “Sheet1″…

This excel file has in fact data that can be read into 2 datasets…i.e. Name / State in one dataset and City/State into another dataset right…We can accomplish that using that using the SAS macros below….

The Macro Call OpenExcelWorkbook opens the excel work book for copy…

%OpenExcelWorkbook(C:\Names.xlsx);

The Macro Call ReadFromExcelTemplate reads the 1st block of excel data from row1/col1 to row7/col2 with 1 first record to be used as column names in the SAS Dataset…

%ReadFromExcelTemplate(dsn1,C:\Names.xlsx,Sheet1,1,1,7,2,yes,15);

The Macro Call ReadFromExcelTemplate reads the 1st block of excel data from row2/col1 to row5/col2 with no record to be used as column names (the macro defaults the var names to col1,col2 etc…) in the SAS Dataset…

%ReadFromExcelTemplate(dsn2,C:\Names.xlsx,Sheet1,2,1,5,2,no,15);

The Macro Call ReadFromExcelTemplate reads the the 2nd block of excel data from row10/col1 to row14/col2 with 1 first record to be used as column names in the SAS Dataset…

%ReadFromExcelTemplate(dsn3,C:\Names.xlsx,Sheet1,10,1,14,2,yes,15);

The Macro Call ReadFromExcelTemplate reads the 2nd block of excel data from row11/col1 to row14/col2 with no record to be used as column names (the macro defaults the var names to col1,col2 etc…) in the SAS Dataset…

%ReadFromExcelTemplate(dsn4,C:\Names.xlsx,Sheet1,11,1,14,2,no,15);

The Macro Call JustCloseExcel closes the excel work book…
%JustCloseExcel;

Other references for Exporting SAS datasets, DDE and MS Excel related…

http://sastechies.blogspot.com/2009/12/export-data-from-sas-into-microsoft.html

Full Code Below

%macro OpenExcelWorkbook(WorkBook);

options noxwait noxsync nomprint nomlogic nosymbolgen;

/* start excel 2007 */

data _null_;

rc=system('start /min excel');

/* talk to DDE, no output data */

x=sleep(4); /*sleep for 4 secs*/

run;

data _null_;

x=sleep(2); /* wait 3 seconds for it to open */

run;

filename DDEcmds dde "excel|system";

data _null_; /* talk to DDE, no output data */

x=sleep(5); /* wait 3 seconds for it to open */

file DDEcmds;

put %unquote(%str(%'[open("&WorkBook.")]%'));

/*put %unquote(%str(%'[workbook.activate("&WorkSheet.")]%'));*/

x=sleep(5); /* wait 3 seconds for it to open */

run;

filename DDEcmds clear;

options mprint mlogic symbolgen;

%mend OpenExcelWorkbook;

%macro ReadFromExcelTemplate(dsn,WorkBook,WorkSheet,StartRow,StartCol,EndRow,EndCol,header,maxcolwidth);

options noxwait noxsync; /* nomprint nomlogic nosymbolgen;*/

%let dlmr='09'x;

%let numvars=%eval(&EndCol. - &StartCol. + 1);

%if %lowcase(&header.) eq yes %then

%do;

/* Read the Header Info First */

FILENAME ReadHdr DDE "EXCEL|&WorkSheet.!R&StartRow.C&StartCol.:R&StartRow.C&EndCol." notab;

DATA _null_;

/* read in the name of the columns with the maxcolwidth */

length

%do r=1 %to &numvars;

ColName&r. $ &maxcolwidth.

%end;

;;

INFILE ReadHdr dlm=&dlmr. dsd missover;

input

%do s=1 %to &numvars;

ColName&s.

%end;

;;

/* assign the colname1, colname2 etc macrovariables with the column names read from the file*/

%do t=1 %to &numvars;

Call symput(compress('ColName'||&t),compress(ColName&t.));

%end;

RUN;

filename ReadHdr clear;

%end;

%else

%do;

/* assign the colname1, colname2 etc macrovariables with the col1 col2 names*/

DATA _null_;

%do t=1 %to &numvars;

Call symput(compress('ColName'||&t),compress("Col&t."));

%end;

RUN;

%end;

%if %lowcase(&header.) eq yes %then

%do;

/* Build DDE FileName Statement to Point to the Right Location on the Excel Spreadsheet */

FILENAME ReadData DDE "EXCEL|&WorkSheet.!R%eval(&StartRow. + 1)C&StartCol.:R&EndRow.C&EndCol." notab;

%end;

%else

%do;

FILENAME ReadData DDE "EXCEL|&WorkSheet.!R&StartRow.C&StartCol.:R&EndRow.C&EndCol." notab;

%end;

DATA &dsn.;

length

%do p=1 %to &numvars;

&&&ColName&p. $ &maxcolwidth.

%end;

;;

INFILE ReadData dlm=&dlmr. dsd missover;

input

%do q=1 %to &numvars;

&&&ColName&q.

%end;

;;

run;

filename ReadData clear;

%mend ReadFromExcelTemplate;

%macro JustCloseExcel;

options noxwait noxsync nomprint nomlogic nosymbolgen;

filename DDEcmds dde "excel|system";

/* Save the Excel file and Quit Excel */

data _null_;

file DDEcmds;

x=sleep(5);

put '[Quit()]';

run;

filename DDEcmds clear;

options mprint mlogic symbolgen;

%mend JustCloseExcel;

%OpenExcelWorkbook(C:\Names.xlsx);

%ReadFromExcelTemplate(dsn1,C:\Names.xlsx,Sheet1,1,1,7,2,yes,15);

%ReadFromExcelTemplate(dsn2,C:\Names.xlsx,Sheet1,1,1,5,2,no,15);

%ReadFromExcelTemplate(dsn3,C:\Names.xlsx,Sheet1,10,1,14,2,yes,15);

%ReadFromExcelTemplate(dsn4,C:\Names.xlsx,Sheet1,11,1,14,2,no,15);

%JustCloseExcel;

You can also download this code from
http://snipplr.com/view/41777/sas-macros-to-read-data-from-ms-excel-using-dde/