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/