using the following would be smarter on completely unknown data;
if vtype(var1)='C' then var1_desired = input(var1, 8.); else var1_desired=var1;
Because I did know a few things up front I went with DBSASTYPE as shown below.
Please comment if you see an error I may have over looked.
options MACROGEN MPRINT mlogic;
%Let PATHER = D:\TEST;
** CREATE THE TABLE WITH COLUMNS BROAD ENCOUGH TO ACCEPT ALL YOUR DATA;
**set the master table;
Data work.master;
attrib
col_1 length =8 format = best25.18
col_2 length =8 format = best25.18
col_3 length =8 format = best25.18
;
Stop;
Run;
PROC SQL NOCONSTDATETIME PRINT;
data xls_files;
keep filename;
length fref $8 filename $80;
rc = filename(fref, "&PATHER");
if rc = 0 then do;
did = dopen(fref);
rc = filename(fref);
end;
else do;
length msg $200.;
msg = sysmsg();
put msg=;
did = .;
end;
if did <= 0 then putlog 'ERR' 'OR: Unable to open directory.';
dnum = dnum(did);
do i = 1 to dnum;
filename = dread(did, i);
fid = mopen(did, filename);
if fid > 0 and index(filename,".xl") then output;
end;
rc = dclose(did);
run;
%macro ReadXls (inf, dir);
libname excellib excel "&dir.&inf"; /* STEP 1 */
proc sql noprint; /* STEP 2 */
create table sheetname as
select tranwrd(tranwrd(Memname,' ',''),'$','') as sheetname
from sashelp.vstabvw
where libname="EXCELLIB";
select count(DISTINCT sheetname) into :cnt_sht
from sheetname;
select DISTINCT sheetname into :sheet1 - :sheet%left(&cnt_sht)
from sheetname;
quit;
libname excellib clear; /* STEP 3 */
%do i=1 %to &cnt_sht;
libname myexcel odbc
required="Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};
dbq=&dir.&inf";
data base;
set myexcel."%sysfunc(TRIM(&sheet&i))$"n
(dbsastype=(col_1= 'NUMERIC' col_2= 'NUMERIC' col_3= 'NUMERIC'
)
);
RUN;
/* STEP 5 */
proc append base=master
data=base force;
run;
%end;
%mend ReadXls;
data _null_;
set xls_files(obs=1);
file_name=filename;
call execute('%nrstr(%ReadXls('||trim(file_name)||', '||"&PATHER"||'))');
run;
data _null_;
set xls_files(firstobs=2);
file_name=filename;
call execute('%nrstr(%ReadXls('||trim(file_name)||', '||"&PATHER"||'))');
run;
... View more
