Posted By

sastechies on 10/07/10


Tagged

import excel Microsoft MS office sas DDe


Versions (?)

SAS Macros to Read data from MS Excel using DDE


 / Published in: SAS
 

URL: http://sastechies.blogspot.com/2010/10/sas-macros-to-read-data-from-ms-excel.html

  1. %macro OpenExcelWorkbook(WorkBook);
  2.  
  3. options noxwait noxsync nomprint nomlogic nosymbolgen;
  4.  
  5. /* start excel 2007 */
  6. data _null_;
  7. rc=system('start /min excel');
  8. /* talk to DDE, no output data */
  9. x=sleep(4); /*sleep for 4 secs*/
  10. run;
  11.  
  12. data _null_;
  13. x=sleep(2); /* wait 3 seconds for it to open */
  14. run;
  15.  
  16. filename DDEcmds dde "excel|system";
  17. data _null_; /* talk to DDE, no output data */
  18. x=sleep(5); /* wait 3 seconds for it to open */
  19. file DDEcmds;
  20. put %unquote(%str(%'[open("&WorkBook.")]%'));
  21. /*put %unquote(%str(%'[workbook.activate("&WorkSheet.")]%'));*/
  22. x=sleep(5); /* wait 3 seconds for it to open */
  23. run;
  24.  
  25.  
  26. filename DDEcmds clear;
  27.  
  28. options mprint mlogic symbolgen;
  29. %mend OpenExcelWorkbook;
  30.  
  31.  
  32.  
  33. %macro ReadFromExcelTemplate(dsn,WorkBook,WorkSheet,StartRow,StartCol,EndRow,EndCol,header,maxcolwidth);
  34.  
  35. options noxwait noxsync; /* nomprint nomlogic nosymbolgen;*/
  36.  
  37. %let dlmr='09'x;
  38.  
  39. %let numvars=%eval(&EndCol. - &StartCol. + 1);
  40.  
  41.  
  42. %if %lowcase(&header.) eq yes %then
  43. %do;
  44. /* Read the Header Info First */
  45. FILENAME ReadHdr DDE "EXCEL|&WorkSheet.!R&StartRow.C&StartCol.:R&StartRow.C&EndCol." notab;
  46. DATA _null_;
  47. /* read in the name of the columns with the maxcolwidth */
  48. length
  49. %do r=1 %to &numvars;
  50. ColName&r. $ &maxcolwidth.
  51. %end;
  52. ;;
  53.  
  54. INFILE ReadHdr dlm=&dlmr. dsd missover;
  55.  
  56. input
  57. %do s=1 %to &numvars;
  58. ColName&s.
  59. %end;
  60. ;;
  61. /* assign the colname1, colname2 etc macrovariables with the column names read from the file*/
  62. %do t=1 %to &numvars;
  63. Call symput(compress('ColName'||&t),compress(ColName&t.));
  64. %end;
  65. RUN;
  66.  
  67. filename ReadHdr clear;
  68. %end;
  69. %else
  70. %do;
  71. /* assign the colname1, colname2 etc macrovariables with the col1 col2 names*/
  72. DATA _null_;
  73. %do t=1 %to &numvars;
  74. Call symput(compress('ColName'||&t),compress("Col&t."));
  75. %end;
  76. RUN;
  77.  
  78. %end;
  79.  
  80.  
  81.  
  82. %if %lowcase(&header.) eq yes %then
  83. %do;
  84. /* Build DDE FileName Statement to Point to the Right Location on the Excel Spreadsheet */
  85. FILENAME ReadData DDE "EXCEL|&WorkSheet.!R%eval(&StartRow. + 1)C&StartCol.:R&EndRow.C&EndCol." notab;
  86. %end;
  87. %else
  88. %do;
  89. FILENAME ReadData DDE "EXCEL|&WorkSheet.!R&StartRow.C&StartCol.:R&EndRow.C&EndCol." notab;
  90. %end;
  91.  
  92.  
  93. DATA &dsn.;
  94. length
  95. %do p=1 %to &numvars;
  96. &&&ColName&p. $ &maxcolwidth.
  97. %end;
  98. ;;
  99.  
  100. INFILE ReadData dlm=&dlmr. dsd missover;
  101. input
  102. %do q=1 %to &numvars;
  103. &&&ColName&q.
  104. %end;
  105. ;;
  106. run;
  107.  
  108. filename ReadData clear;
  109.  
  110. %mend ReadFromExcelTemplate;
  111.  
  112. %macro JustCloseExcel;
  113.  
  114. options noxwait noxsync nomprint nomlogic nosymbolgen;
  115.  
  116. filename DDEcmds dde "excel|system";
  117.  
  118. /* Save the Excel file and Quit Excel */
  119.  
  120. data _null_;
  121. file DDEcmds;
  122. x=sleep(5);
  123. put '[Quit()]';
  124. run;
  125.  
  126. filename DDEcmds clear;
  127.  
  128. options mprint mlogic symbolgen;
  129.  
  130. %mend JustCloseExcel;
  131.  
  132. %OpenExcelWorkbook(C:\Names.xlsx);
  133. %ReadFromExcelTemplate(dsn1,C:\Names.xlsx,Sheet1,1,1,7,2,yes,15);
  134. %ReadFromExcelTemplate(dsn2,C:\Names.xlsx,Sheet1,1,1,5,2,no,15);
  135. %ReadFromExcelTemplate(dsn3,C:\Names.xlsx,Sheet1,10,1,14,2,yes,15);
  136. %ReadFromExcelTemplate(dsn4,C:\Names.xlsx,Sheet1,11,1,14,2,no,15);
  137. %JustCloseExcel;

Report this snippet  

You need to login to post a comment.