topic Re: Extra / appearing in the filename path when filename path is bulit with macro variables in SAS Programming

Extra / appearing in the filename path when filename path is bulit with macro variables

massi — Fri, 25 Apr 2014 17:11:04 GMT

I am trying to import an excel file which resides in a Linux sever.

I am using the following code (running SAS 9.4)

options symbolgen;

%let Datafile=%str(pippo3.xlsx);

%let raw=/sasdata/Projects/share/AGSourcingExcelTool/Superuser;

%let separator = %str(/);

%let aa= %sysfunc(catx(&separator,%nrstr(%superq(raw)),%nrstr(%superq(Datafile))));

filename foo "&aa";

PROC IMPORT

DATAFILE=foo

OUT=Desti replace dbms=xlsx;

*SHEET=Destination; /* (Note use “Sheet 1$”n if spaces in the name) */

RANGE="Destination$A5:Z25000";

GETNAMES=Yes ;/* (Note use No and SAS will assign Var0, Var1 etc) */

RUN;

I get the following error

ERROR: XLSX file does not exist -> /sasdata/Projects/share/AGSourcingExcelTool/Superuser//pippo3.xlsx

As you can see (highlighted in larger red characters) I have an extra "/" coming out in the path which produces the error.

Why is the extra "/" occurring? (Note if I do %put &aa; I do not see any extra "/").

Thank you very much.

Re: Extra / appearing in the filename path when filename path is bulit with macro variables

Quentin — Fri, 25 Apr 2014 17:27:03 GMT

If you do:

%put %sysfunc(pathname(foo)) ;

Do you see the extra slash?

If not, then sounds like maybe an odd proc import bug.

Maybe a work around would be to specify the quoted path/filename on the PROC IMPORT step rather than a file ref.

Re: Extra / appearing in the filename path when filename path is bulit with macro variables

massi — Fri, 25 Apr 2014 17:33:12 GMT

no extra "/" if I use %put %sysfunc(pathname(foo));

Also I tried

options symbolgen;

%let Datafile=%str(pippo3.xlsx);

%let raw=/sasdata/Projects/share/AGSourcingExcelTool/Superuser;

%let separator = %str(/);

%let aa= %sysfunc(catx(&separator,%nrstr(%superq(raw)),%nrstr(%superq(Datafile))));

PROC IMPORT

DATAFILE="&aa"

OUT=Desti replace dbms=xlsx;

*SHEET=Destination; /* (Note use “Sheet 1$”n if spaces in the name) */

RANGE="Destination$A5:Z25000";

GETNAMES=Yes ;/* (Note use No and SAS will assign Var0, Var1 etc) */

RUN;

and I get the same error.

Re: Extra / appearing in the filename path when filename path is bulit with macro variables

data_null__ — Fri, 25 Apr 2014 17:47:36 GMT

I believe that extra delimiters in UNIX path are ignored.

16         filename FT60F001 "//home//zzzzzz///shoes.xlsx"; 
17         proc import datafile=FT60F001 out=xl replace dbms=xlsx;
18            run;

NOTE: The import data set has 395 observations and 7 variables.
NOTE: WORK.XL data set was successfully created.

Re: Extra / appearing in the filename path when filename path is bulit with macro variables

Astounding — Fri, 25 Apr 2014 18:17:49 GMT

It might be as simple as removing %STR from &SEPARATOR:

%let separator = /;

There is no apparent reason that it would need to be quoted. (If you are using it in a %IF comparison that you haven't shown, I'll have to revisit that thought.)

Other notes:

The use of %NRSTR is redundant. Once %SUPERQ has done its thing, %NRSTR has nothing left it can quote.

Unless you have very unusual file names (either containing double quotes, or containing unbalanced single quotes or parentheses), the code can be simplified. For example:

%let aa = &raw/&filename;

If you do have double quotes in the file names, you might need to then use:

filename foo "%superq(aa)";

While it's difficult to be certain (and you do have some high-powered posters looking at this question), I would bet on my first suggestion about defining &SEPARATOR without the %STR function.

Good luck.

Re: Extra / appearing in the filename path when filename path is bulit with macro variables

Kurt_Bremser — Fri, 25 Apr 2014 18:49:32 GMT

Are you absolutely sure that the path works (eg copy it into a cd statement in telnet and the see if ls shows the file)? With that many capital letters there may be an error hiding.

Double slashes are usually compressed to single slashes by UNIX.

I rarely do more than filename xx "&path./&file"; and have no problems.

Re: Extra / appearing in the filename path when filename path is bulit with macro variables

massi — Fri, 25 Apr 2014 19:09:16 GMT

%let aa = &raw/&filename; is how I coded initially and it did not work (same error).

Then in despair (and after a couple of frustrating hours) i did go for "%let aa= %sysfunc(catx(&separator,%nrstr(%superq(raw)),%nrstr(%superq(Datafile))));" with no avail.

The funny thing is the whatever method I use to concatenate the string I check that %put &aa; gives the correct path and yet

it always fail with the double //.

If I try to hard code the path it works with no error. The path is correct (I checked and re checked and rechecked). However I really do not want a hard coded path.

Also with the hard coded path if I insert multiple /// they are ignored as suggested by "data_null_" and I do not get any error.

Re: Extra / appearing in the filename path when filename path is bulit with macro variables

Astounding — Fri, 25 Apr 2014 19:21:24 GMT

When it's a puzzler, collect a little more information. Try inserting this line just above PROC IMPORT:

%put _user_;

Let's see what the macro variables look like at that point.

Re: Extra / appearing in the filename path when filename path is bulit with macro variables

massi — Fri, 25 Apr 2014 19:39:40 GMT

With

options symbolgen;

%let filename=%str(pippo3.xlsx);

%let raw=/sasdata/Projects/share/AGSourcingExcelTool/Superuser;

%let aa = &raw/&filename;

%put _user_;

I have in the log

GLOBAL AA /sasdata/Projects/share/AGSourcingExcelTool/Superuser/ pippo3.xlsx

GLOBAL FILENAME pippo3.xlsx

GLOBAL RAW /sasdata/Projects/share/AGSourcingExcelTool/Superuser

Re: Extra / appearing in the filename path when filename path is bulit with macro variables

Astounding — Fri, 25 Apr 2014 19:44:13 GMT

It looks like there's an extra character in &AA, in between &RAW and &FILENAME. Most likely, that's a "quoting begins here" indicator. Try removing %STR and see if the entire program now works:

%let filename = pippo3.xlsx;

With appendages suitable crossed ...

Re: Extra / appearing in the filename path when filename path is bulit with macro variables

data_null__ — Fri, 25 Apr 2014 19:46:04 GMT

Looks like there is a space between / and pippo3.xlsx in the value of AA

Do you want that?

Re: Extra / appearing in the filename path when filename path is bulit with macro variables

Quentin — Fri, 25 Apr 2014 19:48:02 GMT

Agree with Astounding %put _user_ could help. And also a good suggestion to back off a bit on the macro quoting, unless you have special need for note. Was going to suggest adding %unquote(), but since you use %SYSFUNC not %QSYFUNC all the quoting is removed at that point.

Here is what I see, cant see the slash anywhere. You can see the quoting that was applied to &Separator and &datafile.

16         %let Datafile=%str(pippo3.xlsx);
17         %let raw=/sasdata/Projects/share/AGSourcingExcelTool/Superuser;
18         %let separator = %str(/);
19         %let aa= %sysfunc(catx(&separator,%nrstr(%superq(raw)),%nrstr(%superq(Datafile))));
20         %put _user_;
GLOBAL SEPARATOR    
GLOBAL RAW /sasdata/Projects/share/AGSourcingExcelTool/Superuser
GLOBAL AA /sasdata/Projects/share/AGSourcingExcelTool/Superuser/pippo3.xlsx
GLOBAL DATAFILE  pippo3.xlsx

Re: Extra / appearing in the filename path when filename path is bulit with macro variables

massi — Fri, 25 Apr 2014 19:54:26 GMT

Actually there is not a space (see screenshot)

Re: Extra / appearing in the filename path when filename path is bulit with macro variables

data_null__ — Fri, 25 Apr 2014 19:57:17 GMT

I see it is the quote. Why don't you just %UNQUOTE it and see if that works.

Re: Extra / appearing in the filename path when filename path is bulit with macro variables

massi — Fri, 25 Apr 2014 20:08:36 GMT

I decided to go with the simplest possible code

%let filename=pippo3.xlsx;

%let raw=/sasdata/Projects/share/AGSourcingExcelTool/Superuser;

%let separator =/;

%let aa=&raw.&separator.&filename.;

/*%let aa= %sysfunc(catx(&separator,%nrstr(%superq(raw)),%nrstr(%superq(filename))));*/

%put _user_;

I get

GLOBAL AA /sasdata/Projects/share/AGSourcingExcelTool/Superuser/pippo3.xlsx

GLOBAL FILENAME pippo3.xlsx

GLOBAL RAW /sasdata/Projects/share/AGSourcingExcelTool/Superuser

And now it works!! :smileyshocked:

I don't believe it!!

Re: Extra / appearing in the filename path when filename path is bulit with macro variables

massi — Fri, 25 Apr 2014 20:15:13 GMT

However I am still baffled at what was/is making the double "//"

%let Datafile=%str(pippo3.xlsx);

%let raw=/sasdata/Projects/share/AGSourcingExcelTool/Superuser;

%let separator = %str(/);

%let aa= %sysfunc(catx(&separator,%nrstr(%superq(raw)),%nrstr(%superq(Datafile))));

%put _user_;

I get the error

%let filename=pippo3.xlsx;

%let raw=/sasdata/Projects/share/AGSourcingExcelTool/Superuser;

%let separator =/;

%let aa=&raw.&separator.&filename.;

it works!

:smileyshocked:

Re: Extra / appearing in the filename path when filename path is bulit with macro variables

massi — Fri, 25 Apr 2014 20:19:27 GMT

Thank you for all your support.

I have been banging my head on the wall for a good part of the day with this silly thing.

I am glad at least one version works.

I will survive without knowing why the others version are not working (at least for a couple of days).

Re: Extra / appearing in the filename path when filename path is bulit with macro variables

Cynthia_sas — Fri, 25 Apr 2014 20:34:57 GMT

Hi:

When I try your code (even though I do not think you need the CATX function), I do not get double slashes with either your complex example or any of the simple alternatives (using SAS 9.4).

cynthia

Re: Extra / appearing in the filename path when filename path is bulit with macro variables

DonJefe68 — Wed, 24 Sep 2014 19:58:43 GMT

Not to resurrect a thread, but I've got another piece of data on this:

%let crmms4dt=20140915;
%let crmms2dt=20140811;
%let datadir=/hli/ims/nbtz3jg/sla/prod/data/;

PROC IMPORT OUT=crm_ms4
            DATAFILE= "&datadir.Cumulative SLA Research Reports/Cum SLA Research Report &crmms4dt..xlsx"
            DBMS=XLSX REPLACE;
     SHEET="LLD";
     GETNAMES=YES;
     MIXED=NO;
RUN;

proc import out=crm_assign_request_9b
            DATAFILE= "&datadir.Trigger To Case Create Date/Dashboard CRM Daily &crmms2dt..xlsx"
            DBMS=XLSX REPLACE;
     SHEET="9B";
     GETNAMES=YES;
     MIXED=NO;
RUN;

The first one works and the second one doesn't, failing with the double slash file name error message.

Re: Extra / appearing in the filename path when filename path is bulit with macro variables

mvrieze — Mon, 15 Jun 2020 20:22:59 GMT

This bug still is not fixed.