Loop through csv files and import

[JLin2021]

Hi all

I am trying to edit a macro which can import all csv files in a directory into SAS.

However, it seems there is error if a csv file contains no data (i.e. only header row).

Is there any way to ignore the error? Or to avoid importing those csv files? Thanks!

%macro importfile(location);
filename _dir_ "%bquote(&location.)";
data _null_;
handle = dopen('_dir_');
if handle > 0 then do;
count = dnum(handle);
do i = 1 to count;

if find(dread(handle,i),'csv') > 0 then call execute(cat('Proc Import Out = Work.Testing', i, " DATAFILE= "', &location., dread(handle,i), '"DBMS = csv replace; run;'));
end;
end;
rc=dclose(handle);
run;
filename _dir_ clear;
%mend;

[andreas_lds]

I don't see an easy way to solve the problem, except for replacing proc import by a data step. But this is, of course, hardly possible in your current setup. Do all files have the same structure?

[JLin2021]

Thanks for checking.

I searched that there is a macro which can be used to check whether the file has data or not (https://stackoverflow.com/questions/61742478/importing-empty-csv-file-in-sas)

But then I modify it a bit and it returns error. It only works if I change CSVName to full path&filename, i.e. C:\testing\testing.csv. 

If this macro works, then I can replace my call execute proc import statement by calling this macro:

%macro CheckCSV(CSVName);
data _null_;
if _N_ > 2 or eof then do;
call symputx('Flag', _n_ -1);
stop;
end;
infile "C:\testing\&CSVName" end=eof;
input;
run;

%if &Flag > 1 %then %do;
proc import out = Work.Testing Datafile = "C:\testing\&CSVName" dbms = csv replace;
run;
%end;
%mend;

%CheckCSV("testing.csv")

[Tom]

You need to combine this macro with your other code that is finding the list of file names.

First I would fix the macro a little. Use a more accurate name and pass in the target dataset name along with the CSV filename.

%macro importcsv(CSVName,DSName);
%local flag ;
data _null_;
  call symputx('Flag', _n_ -1);
  infile "&CSVName";
  input;
  if _n_>1 then stop;
run;

%if &Flag > 1 %then %do;
proc import Datafile = "&CSVName" dbms = csv 
  out=&dsname replace
;
run;
%end;
%mend;

Now let's look at how to integrate this into your previous program that is finding the file names.

%macro importfile(location);
data files ;
  length filename $256 dsname $51;
  rc = filename('_dir_',symget('location'));
  if rc then put 'ERROR: Unable to find location.';
  else do;
    handle = dopen('_dir_');
    if handle > 0 then do;
      do i = 1 to dnum(handle);
        filename=catx('/',symget('location'),dread(handle,i));
        dsname = scan(filename,-1,'/\');
        dsname = substrn(dsname,1,min(32,length(dsname)-4));
        dsname = nliteral(dsname);
        if lowcase(scan(filename,-1,'.'))='csv' then do;
          call execute(catx(' ','%nrstr(%importcsv)(',filename,',',dsname,')'))
        ;
      end;
      rc=dclose(handle);
    end;
    else put 'ERROR: Unable to open location as a directory.';
    rc=filename('_dir_');
  end;
  keep filename dsname;
run;
%mend;

[JLin2021]

All files have same structures except that they are generated on different days.

[Kurt_Bremser]

Then you use the data step, and a wildcard in the INFILE statement:

data want;
length fname infilename $200;
infile "/path/*.csv" dlm=',' dsd truncover filename=fname;
infilename = fname; /* puts the current filename into the dataset */
input
  ....
;
run;

[JLin2021]

Thanks a lot! I tried your code and the dataset created contains a no. of duplicated values (all values are full filepath&filename) for those files with data.
Then I create another dataset with distinct values and then loop through each value to execute proc import. Is there smarter way to do?
Sorry I'm new to SAS...
And is there any reason why the CheckCSV macro is not working?

[Kurt_Bremser · Accepted solution]

The code is intended to read all data from all csv files. Add variables as needed (take them from the file documentation) to the INPUT statement (where I put the dots). The filenames from the FILENAME= option are kept only for later reference.

[Sudeep_Neupane]

Does this work for .stc files too?

[Kurt_Bremser]

It works for all text files that can be read with data steps.

[Tom]

Do you really want to run PROC IMPORT independently on each file?  If the files are supposed to contain the same fields then running PROC IMPORT on each one independently will lead to SAS datasets that are not compatible.  So if the files are supposed to have the same structure just read them all in one data step.

data want ;
   length fname filename $256 ;
   infile "&location/*.csv" filename=fname truncover dsd ;
*** Read and hold line so can test if start of a new file ;
   input @;
   if fname ne lag(fname) then input;
   else do;
     filename = fname ;
**** Put Code to read a line from the file here ***;
     input ..... ;
   end;
run;