topic Re: Loop through csv files and import in SAS Programming

Loop through csv files and import

JLin2021 — Fri, 18 Dec 2020 06:40:50 GMT

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;

Re: Loop through csv files and import

andreas_lds — Fri, 18 Dec 2020 07:47:51 GMT

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?

Re: Loop through csv files and import

JLin2021 — Fri, 18 Dec 2020 08:22:57 GMT

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")

Re: Loop through csv files and import

JLin2021 — Fri, 18 Dec 2020 08:24:18 GMT

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

Re: Loop through csv files and import

Kurt_Bremser — Fri, 18 Dec 2020 08:39:49 GMT

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;

Re: Loop through csv files and import

JLin2021 — Fri, 18 Dec 2020 14:06:56 GMT

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?

Re: Loop through csv files and import

Tom — Fri, 18 Dec 2020 16:25:37 GMT

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;

Re: Loop through csv files and import

Tom — Fri, 18 Dec 2020 16:30:24 GMT

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;

Re: Loop through csv files and import

Kurt_Bremser — Fri, 18 Dec 2020 17:08:42 GMT

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.

Re: Loop through csv files and import

Sudeep_Neupane — Thu, 02 Dec 2021 18:04:45 GMT

Does this work for .stc files too?

Re: Loop through csv files and import

Kurt_Bremser — Thu, 02 Dec 2021 18:52:10 GMT

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