SAS - check if external file (.csv) is empty; if not, import into SAS

Question

I have several .csv files in a folder that I would like to import into SAS. However, they are not always populated with data, so when I attempt to import an empty file into SAS, I get an error. I am wondering if there is some way for me to check whether or not an external file is empty, and if it is not, bring it into SAS. This is a code that I would like to automate and not have to manually check and account for blank files every time.

I came across this macro in SAS's knowledge base but am unsure of how to modify it so that it would import a .csv file, if I should be using it at all: http://support.sas.com/kb/25/072.html

I appreciate your help.

Joe Joe · Accepted Answer · 2014-09-12T01:14:52

This is the macro in question from the link:

%macro test(outf);
 %let filrf=myfile;
 %if %sysfunc(fileexist(&outf)) %then %do;
 %let rc=%sysfunc(filename(filrf,&outf));
 %let fid=%sysfunc(fopen(&filrf));
  %if &fid > 0 %then %do;
   %let rc=%sysfunc(fread(&fid));
   %let rc=%sysfunc(fget(&fid,mystring));
    %if &rc = 0 %then %put &mystring;
    %else %put file is empty;
   %let rc=%sysfunc(fclose(&fid));
  %end;
%let rc=%sysfunc(filename(filrf));
%end;
%else %put file does not exist;
%mend test;

%test(c:\test.txt)

What you'd want to do is change what the macro does in the case that the file is empty. In this case, the fget will return a 0 to the return code (%let rc = ... fget) if it is able to get a string, or it will fail and return a nonzero code.

So, just modify the %if / %else. Instead of putting something, you just do something like

%if &rc=0 %then %do;
  %import_csv(&outf.);
%end;
%else %do;
  %put File &outf. is empty.;
%end;

That assumes you have a macro that does your import. You could, of course, include the full PROC IMPORT code there instead.

This really makes a reasonable error checking wrapper to an import macro, in fact.

%macro import_csv(outf=,outds=);
 %let filrf=myfile;
 %if %sysfunc(fileexist(&outf)) %then %do;
 %let rc=%sysfunc(filename(filrf,&outf));
 %let fid=%sysfunc(fopen(&filrf));
  %if &fid > 0 %then %do;
   %let rc=%sysfunc(fread(&fid));
   %let rc=%sysfunc(fget(&fid,mystring));
   %let rc_close=%sysfunc(fclose(&fid));
    %if &rc = 0 %then %do;
       proc import file="&outf." out="&outds."
                   dbms=csv replace;
       run;
    %end;
    %else %put File &outf. is empty and not imported.;
  %end;
 %let rc=%sysfunc(filename(filrf));
 %end;
 %else %put file does not exist;
%mend test;

SAS - check if external file (.csv) is empty; if not, import into SAS

1 Answers