I'd do this:
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á!
Your Excel file should contain two values: the name of the file to be imported, and the name of the resulting dataset. Very often, filenames can't be used as dataset names.
Import the Excel file, and use a DATA _NULL_ step to build your import code for each entry with CALL EXECUTE. Or write the code to a temporary file, which you later %INCLUDE.
Thank you for reply. Im still new to this and i kind of hard to imagine on writing the code. Anyway, Thank you for your time 🙂
I'd do this:
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á!
Hi,
Thank you for your reply.
I still trying to get the code ready as I stumbled to error codes below
ERROR: The XLSX engine cannot be found.
ERROR: Error in the LIBNAME statement.
I changed the XLSX to EXCEL and XLS also will show error (currently working on SAS Enterprise Guide 8.1). I will try to find way how to solve the issue and test the code again.
Thank you 🙂
Hi,
I change the excel file to become CSV and some tweak here & there and it works now.
Thank you for the sample solution.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.