DATA Step, Macro, Functions and more

Multiple days CSV file import to SAS

Accepted Solution Solved
Reply
Contributor scb
Contributor
Posts: 69
Accepted Solution

Multiple days CSV file import to SAS

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.

Attachment

Accepted Solutions
Solution
‎01-26-2017 08:48 PM
Super User
Posts: 7,863

Re: Multiple days CSV file import to SAS

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);
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super Contributor
Posts: 474

Re: Multiple days CSV file import to SAS

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

Super User
Posts: 7,863

Re: Multiple days CSV file import to SAS

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Solution
‎01-26-2017 08:48 PM
Super User
Posts: 7,863

Re: Multiple days CSV file import to SAS

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);
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor scb
Contributor
Posts: 69

Re: Multiple days CSV file import to SAS

Posted in reply to KurtBremser

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;

Super User
Posts: 7,863

Re: Multiple days CSV file import to SAS

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.

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Trusted Advisor
Posts: 1,586

Re: Multiple days CSV file import to SAS

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;

 

 

Contributor scb
Contributor
Posts: 69

Re: Multiple days CSV file import to SAS

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.

Super User
Posts: 7,863

Re: Multiple days CSV file import to SAS


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);
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Trusted Advisor
Posts: 1,586

Re: Multiple days CSV file import to SAS

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;
Super Contributor
Posts: 266

Re: Multiple days CSV file import to SAS

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;

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 420 views
  • 0 likes
  • 5 in conversation