read a list of file names with blanks by filename pipe

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

read a list of file names with blanks by filename pipe

Hi,

 

I'm recently doing a project which needs to read multiple files into SAS datasets. I have a problem when using filename pipe to read file names with blanks it them. Here is my code:

 

filename babycsv pipe 'dir "G:\Original_Data\*.csv" /b';
data babycsv;
length fname file csv $200.;
infile babycsv;
input fname $;
file='SAS_'||tranwrd(scan(fname,1,'.'),'-','_');
csv='G:\Original_Data\\'||fname;
call symput('num_files', put(_n_,best.));
run;

 

The csv files are like this form: Infant 2011.csv, with space in it.

Consequently, my SAS output is like:

fname file csv       

infant  SAS_infant G:\Original_Data\Infant

 

Is there any way to output the entire file name rather than spliting by blank?

 

Thanks,

Hera


Accepted Solutions
Solution
‎01-28-2016 09:19 AM
Super User
Posts: 6,932

Re: read a list of file names with blanks by filename pipe

Try this:

filename babycsv pipe 'dir "G:\Original_Data\*.csv" /b';
data babycsv;
length fname file csv $200.;
infile babycsv truncover;
input fname $200.;
file='SAS_'||tranwrd(scan(fname,1,'.'),'-','_');
csv='G:\Original_Data\\'||fname;
call symput('num_files', put(_n_,best.));
run;

using formatted input with the truncover option and a sufficient length, you make sure that the whole line is read into the variable.

Another way would be

filename babycsv pipe 'dir "G:\Original_Data\*.csv" /b';
data babycsv;
length fname file csv $200.;
infile babycsv;
input;
fname = _infile_;
file='SAS_'||tranwrd(scan(fname,1,'.'),'-','_');
csv='G:\Original_Data\\'||fname;
call symput('num_files', put(_n_,best.));
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Posts: 10,483

Re: read a list of file names with blanks by filename pipe

I'm guessing a little here because you don't show what the actually DIR result (the input) is.

Since the /b modifier just generates the file name what you might do is provide a delimiter on your infile statement that never appears in your file names maybe something like:

infile babycsv dlm='@';

Then the spaces should be ignored.

 

Also instead of

csv='G:\Original_Data\\'||fname;

you might try

csv=cats('G:\Original_Data\\',fname);

to prevent

Solution
‎01-28-2016 09:19 AM
Super User
Posts: 6,932

Re: read a list of file names with blanks by filename pipe

Try this:

filename babycsv pipe 'dir "G:\Original_Data\*.csv" /b';
data babycsv;
length fname file csv $200.;
infile babycsv truncover;
input fname $200.;
file='SAS_'||tranwrd(scan(fname,1,'.'),'-','_');
csv='G:\Original_Data\\'||fname;
call symput('num_files', put(_n_,best.));
run;

using formatted input with the truncover option and a sufficient length, you make sure that the whole line is read into the variable.

Another way would be

filename babycsv pipe 'dir "G:\Original_Data\*.csv" /b';
data babycsv;
length fname file csv $200.;
infile babycsv;
input;
fname = _infile_;
file='SAS_'||tranwrd(scan(fname,1,'.'),'-','_');
csv='G:\Original_Data\\'||fname;
call symput('num_files', put(_n_,best.));
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 5

Re: read a list of file names with blanks by filename pipe

Thank you! It works now. I do agree it causes trouble when there are blanks in file names.
Super User
Posts: 5,256

Re: read a list of file names with blanks by filename pipe

Another option is to use data step functions to the file names. They give you better control but requires a bit more logic.
Data never sleeps
Super User
Super User
Posts: 7,399

Re: read a list of file names with blanks by filename pipe

Are the CSV files all the same, e.g. all variables present, matching format?  If so you can call one command in DOS:

copy <directory>\*.csv total.csv

This will create one CSV file with all the .csv.  That one file can then be read in.  However that will not work if there is any differences, also not that header rows would be a problem.

Super User
Posts: 6,932

Re: read a list of file names with blanks by filename pipe

Oh, and if I haven't said it already: blanks in filenames are a BAD IDEA.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is SOLVED.

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

Discussion stats
  • 6 replies
  • 458 views
  • 0 likes
  • 5 in conversation