DATA Step, Macro, Functions and more

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

Reply
Occasional Contributor
Posts: 14

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

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.

PROC Star
Posts: 1,324

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

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.

Occasional Contributor
Posts: 14

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

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.

Respected Advisor
Posts: 3,799

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

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.
Super User
Posts: 5,516

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

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.

Occasional Contributor
Posts: 14

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

Posted in reply to Astounding

%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.


 

Super User
Posts: 5,516

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

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.

Occasional Contributor
Posts: 14

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

Posted in reply to Astounding

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

Super User
Posts: 5,516

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

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 ...


Respected Advisor
Posts: 3,799

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

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

Do you want that?

Occasional Contributor
Posts: 14

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

Posted in reply to data_null__

Actually there is not a space (see screenshot)

screenshot.png

Respected Advisor
Posts: 3,799

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

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

Occasional Contributor
Posts: 14

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

Posted in reply to data_null__

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!!

Occasional Contributor
Posts: 14

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

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:



Occasional Contributor
Posts: 14

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

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).

Ask a Question
Discussion stats
  • 18 replies
  • 2748 views
  • 0 likes
  • 7 in conversation