| This post was kindly contributed by SASTechies - go there to comment and to read the full post. |
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
| This post was kindly contributed by SASTechies - go there to comment and to read the full post. |