macro for load in multiple files csv

[sasprogramming]

options compress=Yes;

data salesdata_all;
 %let _EFIERR_ = 0; /* set the ERROR detection macro variable
 infile 'C:\Users\peter\salesdata_jun2020.csv' delimiter = ',' MISSOVER DSD TERMSTR=CRLF  lrecl=32767 
 
 firstobs=2 ;
 informat ACCOUNT_NUMBER $32. ;
 informat FACILITY_CREATION_DATE anydtdtm40. ;
 format ACCOUNT_NUMBER $32. ;
 format FACILITY_CREATION_DATE datetime. ;
 input
 ACCOUNT_NUMBER $
 FACILITY_CREATION_DATE
 ;
 if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
 run;
 

 Above is my SAS code for loading in the jun2020 sales data (csv format)

I have a lot of similar files that I would like to load in concatenate together.

They are:

• salesdata_jun2020
• salesdata_july2020
• salesdata_aug-sep2020
• salesdata_oct2020

etc...

As you can see they aren't always the same file name (with the month changed). I am struggling to write a macro to import all of this data and then concatenate together, rather than doing it manually. Any help would be appreciated.

Thanks

[andreas_lds]

As long as all files share a common prefix and have the same suffix, you don't have to write a macro, using a wildcard in the infile-statement will automatically process all matching files. Unfortunately it seems that the files have an header-line. If you can't get those files without them, some more lines have to be written. Here's an example using sashelp.class:

%let data_dir = PATH_TO_ANY_DIR;

proc export data=sashelp.class(where=(Age <= 12)) dbms=csv file="&data_dir\class_one.csv" replace;
run;

proc export data=sashelp.class(where=(Age > 12)) dbms=csv file="&data_dir\class_another.csv" replace;
run;


data all;
   length 
      Name $ 10
      Sex $ 1
      Age Height Weight 8

      _filename fname $ 100
   ;
   retain fname;

   infile "&data_dir\class*.csv" dsd filename= _filename;

   input @;

   if _filename ^= fname or missing(_infile_) then do;
      fname = _filename;
   end;
   else do;
      input Name -- Weight;
      output;
   end;
run;

[Kurt_Bremser]

Naming files like that makes your work unnecessarily hard. For timestamps in filenames, always use a numeric YM(D) date order. Files will sort correctly, and you can use wildcards with much more confidence.

Even with multi-month files, 202008-09 will be much easier to handle than aug-sep2020.

If you can't use wildcards, read the filenames in a directory by using the DOPEN, DNUM and DREAD functions; store the filtered filenames in a dataset, which you can then use to dynamically read all files in a single data step by using the FILEVAR= option of the INFILE statement.

%let inpath=C:\Users\peter;

data files;
length
  dref $8
  fname $200
;
rc = filename(dref,"&inpath.");
did = dopen(dref);
if did
then do;
  do i = 1 to dnum(did);
    fname = dread(did,i);
    if /* filter here */
    then do;
      fname = catx("\","&inpath.",fname);
      output;
    end;
  end;
  rc = dclose(did);
end;
rc = filename(dref);
keep fname;
run;

data want;
set files;
infile dummy filevar=fname end=done /* other options */;
do while (not done);
  input .....;
  output;
end;
run;

[sasprogramming]

Thanks, I am trying to understand where I would put in my data load in code, is this right?

%let inpath=C:\Users\peter;

data files;
length
  dref $8
  fname $200
;
rc = filename(dref,"&inpath.");
did = dopen(dref);
if did
then do;
  do i = 1 to dnum(did);
    fname = dread(did,i);
    if /* filter here */
    then do;
      fname = catx("\","&inpath.",fname);
      output;
    end;
  end;
  rc = dclose(did);
end;
rc = filename(dref);
keep fname;
run;

data want;
set files;
infile dummy filevar=fname end=done delimiter = ',' MISSOVER DSD TERMSTR=CRLF  lrecl=32767  /* other options */;
do while (not done);
 firstobs=2 ;
 informat ACCOUNT_NUMBER $32. ;
 informat FACILITY_CREATION_DATE anydtdtm40. ;
 format ACCOUNT_NUMBER $32. ;
 format FACILITY_CREATION_DATE datetime. ;
 input
 ACCOUNT_NUMBER $
 FACILITY_CREATION_DATE
 ;
 if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
end;
run;

[Tom]

That looks pretty close.  What errors did you get?   

You need to put the FIRSTOBS=2 option into the INFILE statement. The way you have it now it is an assignment statement creating a dataset variable named FIRSTOBS that is always going to have the value 2.

You did not put any condition into the IF statement.  That should be replaced by something like:

if scan(fname,-1,'.')='csv' then do;

so that only the CSV files are kept in the list of files.  You could of course move the filtering to the next step, where you read in the list of files from the data step generated by the first step, to only read the subset of the files in the directory that match your condition.

Note you probably want to use the TRUNCOVER option instead of MISSOVER option. You almost NEVER want SAS to set the value to missing if there are not enough characters on the line to fully meet the informat used. You would rather that the truncated value be used instead.

You also do not need to set the _EFIERR_ macro variable. That is something that PROC IMPORT uses internally.

[Kurt_Bremser · Accepted solution]

Corrected data step:

data want;
set files;
/* move global statements out of the loop, for better code readability */
length
  ACCOUNT_NUMBER $32.
;
/* use LENGTH to define character variables, not FORMAT or INFORMAT */
informat
  FACILITY_CREATION_DATE anydtdtm40.
;
format
  FACILITY_CREATION_DATE datetime.
;
infile dummy filevar=fname end=done delimiter = ',' truncover dsd termstr=CRLF firstobs=2;
/* firstobs is an option of the INFILE statement */
do while (not done);
  input
   ACCOUNT_NUMBER 
   FACILITY_CREATION_DATE
  ;
  if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
  output; /* or you'll only get the last observation of each file! */
end;
run;

Complete example, tested on SAS On Demand:

data _null_;
set sashelp.class;
file "~/class1.csv" dlm=",";
if _n_ = 1 then put "name,sex";
put name sex;
run;

data _null_;
set sashelp.class;
file "~/class2.csv" dlm=",";
if _n_ = 1 then put "name,sex";
put name sex;
run;

%let inpath=~;

data files;
length
  dref $8
  fname $200
;
rc = filename(dref,"&inpath.");
did = dopen(dref);
if did
then do;
  do i = 1 to dnum(did);
    fname = dread(did,i);
    if scan(fname,-1,".") = "csv" and substr(fname,1,5) = "class"
    then do;
      fname = catx("/","&inpath.",fname);
      output;
    end;
  end;
  rc = dclose(did);
end;
rc = filename(dref);
keep fname;
run;

data want;
set files;
infile dummy filevar=fname end=done dlm="," firstobs=2;
do while (not done);
  input name :$8. sex :$1.;
  output;
end;
run;

Note the different separator (forward slash instead of backslash) used in the filename because of UNIX. Otherwise that same code will work in your SAS.