BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
JLin2021
Fluorite | Level 6

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;
1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

10 REPLIES 10
andreas_lds
Jade | Level 19

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
Fluorite | Level 6

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
Super User Tom
Super User

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
Fluorite | Level 6
All files have same structures except that they are generated on different days.
Kurt_Bremser
Super User

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
Fluorite | Level 6
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
Super User

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
Obsidian | Level 7

Does this work for .stc files too?

 

 

Tom
Super User Tom
Super User

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;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 2564 views
  • 4 likes
  • 5 in conversation