topic Re: import excel files when using filename indata pipe but the directory has spaces in SAS Programming

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

daisy6 — Wed, 08 Nov 2017 21:17:06 GMT

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.

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

SuryaKiran — Wed, 08 Nov 2017 21:30:15 GMT

Hi,

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

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

daisy6 — Wed, 08 Nov 2017 21:37:38 GMT

What do you mean? Could you please explain more?

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

Reeza — Wed, 08 Nov 2017 21:54:28 GMT

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"

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

error_prone — Wed, 08 Nov 2017 21:51:50 GMT

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"

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

daisy6 — Wed, 08 Nov 2017 22:01:24 GMT

Thanks for the reply! Reeza

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

daisy6 — Wed, 08 Nov 2017 22:01:54 GMT

It works, Thanks for the reply!