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;
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.
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?
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")
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;
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;
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.
Does this work for .stc files too?
It works for all text files that can be read with data steps.
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;
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 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.