@dirks
To be very clear: I'm only using such a conversion step when designing/implementing for "production worthy" ETL processes where I had to accept Excels as data sources; especially when using DIS.
My main reason for such an approach is to "keep the mess" in a separate job (the conversion to text files) and that once things fell over often enough in production because of an "instable" Excel data provision, "someone" will understand and make the effort to provide reasonable data sources - and then I can hopefully negotiate to get an interface contract which defines the text files I've already created - so one needs only to deactivate the conversion jobs.
Below the code sample you've asked for. If using DIS I'd implement this as a custom transformation passing in the parameter values via prompts.
/* macro to read Excel sheet and convert it to text file */
%macro xlsx2txt
(
infile,
sheet_range,
outfile,
dlm
);
%if %str(&dlm)=%str() %then %let dlm=|;
/* Proc Import step */
proc import
datafile="&infile"
dbms=xlsx
out=work._temp_1234
replace;
range="&sheet_range"
;
getnames=NO;
run;
/* remove rows where all cells are empty */
data work._temp_1234;
set work._temp_1234;
if cmiss(of _all_)>0 then delete;
run;
/* Proc Export step */
proc export
data=work._temp_1234
outfile="&outfile"
dbms=dlm
replace;
delimiter="&dlm";
putnames=no;
run;
/* delete temporary work table */
proc datasets lib=work nolist nowarn;
delete _temp_1234;
run;
quit;
%mend;
/* directory for sample data */
%let path=c:\test;
/* create sample Excel workbook and sheet */
libname test xlsx "&path\sample.xlsx";
data test.'sample sheet'n;
set sashelp.class;
format dttm_var datetime20.;
dttm_var=datetime();
run;
libname test clear;
/* execute macro */
%xlsx2txt(&path\sample.xlsx,sample sheet$a2:f10000000,&path\sample.txt);
