Advanced Search

SAS Macros to Read data from MS Excel using DDE

/ Published in: SAS
Save to your folder(s)

Expand | Embed | Plain Text

Copy this code and paste it in your 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;

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

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.