topic Re: Multiple days CSV file import to SAS in SAS Programming

Multiple days CSV file import to SAS

scb — Wed, 25 Jan 2017 07:59:56 GMT

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.

Re: Multiple days CSV file import to SAS

DanielSantos — Wed, 25 Jan 2017 08:38:55 GMT

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

Re: Multiple days CSV file import to SAS

Kurt_Bremser — Wed, 25 Jan 2017 08:40:32 GMT

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.

Re: Multiple days CSV file import to SAS

Kurt_Bremser — Wed, 25 Jan 2017 08:43:59 GMT

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);

Re: Multiple days CSV file import to SAS

Shmuel — Wed, 25 Jan 2017 08:46:16 GMT

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;

Re: Multiple days CSV file import to SAS

RahulG — Wed, 25 Jan 2017 09:00:10 GMT

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;

Re: Multiple days CSV file import to SAS

scb — Wed, 25 Jan 2017 09:23:48 GMT

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.

Re: Multiple days CSV file import to SAS

Kurt_Bremser — Wed, 25 Jan 2017 09:27:38 GMT

@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);

Re: Multiple days CSV file import to SAS

Shmuel — Wed, 25 Jan 2017 09:34:41 GMT

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;

Re: Multiple days CSV file import to SAS

scb — Wed, 25 Jan 2017 10:18:43 GMT

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;

Re: Multiple days CSV file import to SAS

Kurt_Bremser — Wed, 25 Jan 2017 11:39:37 GMT

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.