import excel files when using filename indata pipe but the directory has spaces

[daisy6]

Hi SAS experts,

I use filename pipe to read some excel files into SAS. The directories are macros and also have some spaces. The codes I usually uses do not work this time. As far as I know, if the directory has no space, my code works.  Here is my code.

%let dir=c:\projects\2017\NFL;

%let fold1= National Football League (NFL);

filename indata pipe "dir &dir.\&fold1. /d" 
data file_list;
length fname$ 256.;
infile indata truncover length=reclen; /* infile statement for file names */
input fname $256.; /* read the file names from the directory */
run;

the log window shows:

Stderr output:
File Not Found
File Not Found
NOTE: 17 records were read from the infile INDATA.
The minimum record length was 0.
The maximum record length was 38.
NOTE: The data set WORK.FILENAMES has 17 observations and 1 variables.
NOTE: DATA statement used (Total process time):
real time 0.09 seconds
cpu time 0.03 seconds

The filenames dataset :

Volume in drive C is OSDisk

Volume Serial Number is ....

Directory of C:\projects\2017\NFL

Please give me some hints to solve the problem. 

[SuryaKiran]

Hi,

Did you try using the folder name like 'folder name with space'n

[daisy6]

What do you mean? Could you please explain more?

[error_prone · Accepted solution]

You have to enclose the path in quotes in the dir-command, because it contains chars which should be avoided when creating directories. Not easy to test on my mac ... maybe something like

filename indata pipe "dir ""&dir.\&fold1."" /d" 

[daisy6]

It works, Thanks for the reply!

[Reeza]

The 'directory' in this case does have spaces, look at your code:

%let dir=c:\projects\2017\NFL;

%let fold1= National Football League (NFL);

filename indata pipe "dir &dir.\&fold1. /d" 

which becomes, I've noted in red the spaces. 

filename indata pipe "dir c:\projects\2017\NFL\National_Football_League_(NFL) /d" 

So you need to quote it properly to allow those to resolve, try the following (sorry I hit enter earlier on this post):

filename indata pipe "dir ""&dir.\&fold1."" /d" 

[daisy6]

Thanks for the reply! Reeza