SAS to import multiple data where the file path&Name in Excel

[ChiefYuri]

Hi All, I have a situation where I need to import multiple files, and the file path and file name is in excel. Example: I have to import 3 text files from 3 different locations (locations are keep changing). I have 1 excel file where it has information on file path in Column A and filename in Column B. How do I import all 3 files into the SAS program. Please help me. Thank you in advance.

[Kurt_Bremser]

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.

[ChiefYuri]

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 🙂

[SASJedi · Accepted solution]

I'd do this:

1. import the Excel file
2. In a DATA _NULL_ step:
   1. Use COMPRESS to convert the file name, which may include unacceptable characters, to a valid SAS table name.
   2. Construct PROC IMPORT step code to import the file
   3. 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á!

[ChiefYuri]

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 🙂

[ChiefYuri]

Hi,

I change the excel file to become CSV and some tweak here & there and it works now.

Thank you for the sample solution.

[SASJedi]

My pleasure! Stay SASy, my friend 🙂