sqlExcel.sas

/*********************************************************************************\
PROGRAM INFORMATION
Project : Autocall Macros
Purpose : Import a sheet from an xls file via a SQL pass through query
Inputs  : xls file
Outputs : SAS dataset
Notes   : data  - Dataset to be created.
          path  - Path to the xls worksheet to be imported.
          sheet - Name of the worksheet to be imported.
          where - optional where clause to limit data;
Sample  : %sqlExcel(data=work.results, path="&ROOT\Results.xls", sheet='Results');

PROGRAM HISTORY
2015-02-18 RK Initial coding.
\*********************************************************************************/;
 
%macro sqlExcel(cmd,
data=
,path=
,sheet=
,range=
,where=
,mixed=NO
);
 
%let cmd=%upcase(&CMD);
 
	%if "&CMD" eq "LIST" %then %do;
		libname _xlfile &PATH access=readonly;
		proc datasets lib=_XLFILE nodetails;
		quit;
		libname _xlfile clear;
		dm log 'show';
	    %goto exit;
	%end;
 
	data _null_;
		format sheet range sheetrange $100.;
		call missing(sheet, range);
		
		%if &SHEET ne %then sheet=compress(&sheet,'''"$');; *** no quotes and no $ ***;
		%if &RANGE ne %then range=compress(&range,'''"' );; *** no quotes, $ is okay ***;
 
		*** if range contains `$`, assume it is the whole thing. ***;
		if index(range, '$') then sheetrange=range;
		else sheetrange=cats(sheet, '$', range);
		call symput('source', cats('[', sheetrange, ']')); *** wrap source in square brackets ***;
	run;
 
	proc sql;
		connect to excel(path=&PATH mixed=&MIXED);
		%put &SQLXRC: &SQLXMSG;
		create table &DATA %if "&WHERE" ne "" %then %do; (where=(&WHERE)) %end; as
		select * from connection to excel ( select * from &source );
		%put &SQLXRC: &SQLXMSG;
		disconnect from excel;
	quit;
	%exit:
%mend sqlExcel;