Multiple days CSV file import to SAS

[scb]

Every month I have daily CSV files (from 2nd day of the month to 1st day of next month, e.g. 2 Oct to 1 Nov).  I have no problem in importing the CSV file individually into SAS.  May I know how to import all 31 days CSV in one csv import statement by controlling the date?

Attached is 2 CSV files, the last 6 digit is the date of the file in format ddmmyyyy.

Any idea? Thanks.

[DanielSantos]

Hi.

That may depend on how you are importing those files.

Please share the code that you are using for importing one file.

Daniel Santos @ www.cgd.pt

[Kurt_Bremser]

You can concatenate filenames in a filename statement:

filename in ('file1.dat','file2.dat','file3.dat');

Those files will be read sequentially when used as infile in a data step.

[Kurt_Bremser · Accepted solution]

To expand on my first answer, see this example code:

%let month=1;
%let year=2017;
%let file_prefix=datafile_;
%let file_suffix=dat;

data _null_;
start = mdy(&month,2,&year);
end = intnx('month',start,1,'begin');
length filestring $1000 file_name $30;
do date = start to end;
  file_name = "'&file_prefix" !! put(date,ddmmyyn8.) !! ".&file_suffix'";
  filestring = catx(',',filestring,file_name);
end;
call symput('filestring',trim(filestring));
run;

%put &filestring;

filename in (&filestring);

[scb]

The codes help and I have added the proc import codes.  When I run the proc import codes and use the file_name, I can only import the last day of the data instead of all 31 (days) files.  Any comments? Thanks.

%let month=1;
%let year=2017;
%let file_prefix=MB0101D;
%let file_suffix=txt;

data _null_;
start = mdy(&month,2,&year);
end = intnx('month',start,1,'begin');
length file_name $30;

do date = start to end;
file_name = "&file_prefix" !! put(date,ddmmyyn8.) !! ".&file_suffix";
call symput('file_name',trim(file_name));
end;

proc import datafile="D:\&file_name" out=test dbms=TAB replace;
getnames=no;
run;

[Kurt_Bremser]

Compare my code:

data _null_;
start = mdy(&month,2,&year);
end = intnx('month',start,1,'begin');
length filestring $1000 file_name $30;
do date = start to end;
  file_name = "'&file_prefix" !! put(date,ddmmyyn8.) !! ".&file_suffix'";
  filestring = catx(',',filestring,file_name);
end;
call symput('filestring',trim(filestring));
run;

with yours:

data _null_;
start = mdy(&month,2,&year);
end = intnx('month',start,1,'begin');
length file_name $30;
do date = start to end;
  file_name = "&file_prefix" !! put(date,ddmmyyn8.) !! ".&file_suffix";
  call symput('file_name',trim(file_name));
end;

and find the difference. You omitted the accumulation of filenames into a string, and moved the call symput into the do loop. Only the result of the last call symput will "survive", and that goes into the proc import. My code uses a fileref, which allows multiple filenames.

I also recommend abandoning the guesssing procedure PROC IMPORT in favor of a data step written to file specifications.

Within a data step, you can also take care of header lines that will appear everytime a new input file starts.

[Shmuel]

As you have a code to read individual CSV file , we don't need the files but only their names,

that is:  from MB0101D31102016.csv to MB0101D01112016.csv;

Then create a filename to include all those files:

%let start_date=31OCT2016;

%let end_date = 01NOV2016;

data _NULL_;

        length fnames $1000;

       st = input("&start_date", date9.);

       nd = input("end_date",date9.);

       do i=st to nd by 1;

            datex = put(i,ddmmyyn8.);

            fname = 'MB0101D'||datex||'.csv';

            fnames = catx('" "', fnames,fanme);

      end;

      fnames = '"'||fnames||'"';

      rc = filename csv_all (fnames);

end;

data want;

       infile csv_all truncover;

        ... your code to read variables ...

run;

[scb]

I got this error message, is it due to the SAS version, mine is SAS 9.2.

rc = filename csv_all (fnames);
-------
22
68
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, -, /, ;, <,
<=, <>, =, >, ><, >=, AND, EQ, GE, GT, LE, LT, MAX, MIN, NE, NG, NL, OR, [, ^=,
{, |, ||, ~=.

ERROR 68-185: The function CSV_ALL is unknown, or cannot be accessed.

[Kurt_Bremser]

---

@scb wrote:

I got this error message, is it due to the SAS version, mine is SAS 9.2.

rc = filename csv_all (fnames);
-------
22
68
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, -, /, ;, <,
<=, <>, =, >, ><, >=, AND, EQ, GE, GT, LE, LT, MAX, MIN, NE, NG, NL, OR, [, ^=,
{, |, ||, ~=.

ERROR 68-185: The function CSV_ALL is unknown, or cannot be accessed.

---

I guess that should have been

rc = filename ('csv_all',fnames);

[Shmuel]

Sorry, I had few mis types. Here is a tested code:

%let start_date=31OCT2016;
%let end_date = 01NOV2016;
data _NULL_;
        length fnames $1000;
       st = input("&start_date", date9.);
       nd = input("&end_date",date9.);
       do i=st to nd by 1;
            datex = put(i,ddmmyyn8.);
            fname = 'MB0101D'||datex||'.csv';
            fnames = catx('" "', fnames,fname);
      end;
      fnames = '"'||fnames||'"';
      rc = filename('csv_all',fnames);
run;

[RahulG]

You should create a macro %import_file()  what will import csv file and this macro would need date parameter.

%macro import_file(date_val);

proc import data='MB101&date_val'
   out = temp_F
  replace;
run;
%mend;


data _null_;
t =today();
first_day=intnx('month',today(),0,'b');
last_day=intnx('month',today(),0,'e');
format t first_day  last_day counter ddmmyyn8. ;
counter=first_day;
do while (counter <=last_day);
counter+1;
put '%import_file(' counter ');';
end;
run;