Oct 7, 2010

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″…
blog1
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);
blog2
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);
blog3
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);
blog4
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);
blog5
The Macro Call JustCloseExcel closes the excel work book…
%JustCloseExcel;
Other references for Exporting SAS datasets, DDE and MS Excel related…
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;