I'd do this:
import the Excel file
In a DATA _NULL_ step:
Use COMPRESS to convert the file name, which may include unacceptable characters, to a valid SAS table name.
Construct PROC IMPORT step code to import the file
Use CALL EXECUTE to execute the command.
Here is a template for the process. The first part just builds some sample files for testing:
/* Create the data used for testing */
%let path=c:\temp;
libname x xlsx "&path\files.xlsx";
data x.sheet1;
infile datalines dsd truncover;
input a:$15. b:$21.;
datalines;
c:\temp,file1.csv
c:\temp,poorly named file.csv
;
libname x clear;
data _null_;
file "c:\temp\file1.csv";
put "ID,Name";
put "1,Sam";
put "2,Sally";
file "c:\temp\poorly named file.csv";
put "ID,Name";
put "3,Ansel";
put "4,Anne";
run;
Now that we have some files to play with, here is a template for doing the deed:
/* Import the file list data from the Excel file */
proc import datafile = "&path\files.xlsx"
dbms=xlsx
out =work.fileList replace;
/* if your Excel file had no headler row */
/* getnames=no*/
;
run;quit;
data _null_;
set work.fileList;
/*extract the file name without extension*/
tableName=scan(b,1,'\/.');
/*Remove any characters invlaid for SAS names from the file name*/
tableName=compress(tableName,,'kn');
/* construct the command to import the current file*/
command=catx(' '
,cats('PROC IMPORT OUT= WORK.',tableName)
,cats('DATAFILE="',a,'\',b,'"')
,'DBMS=CSV REPLACE; GETNAMES=YES;run; quit;'
);
put command=;
/* Import the file */
call execute(command);
run;
And - voilá!
... View more
