BookmarkSubscribeRSS Feed
massi
Calcite | Level 5

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.

19 REPLIES 19
Quentin
Super User

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.

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
massi
Calcite | Level 5

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.

data_null__
Jade | Level 19

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.
Astounding
PROC Star

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.

massi
Calcite | Level 5

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


 

Astounding
PROC Star

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.

massi
Calcite | Level 5

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

Astounding
PROC Star

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


data_null__
Jade | Level 19

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

Do you want that?

massi
Calcite | Level 5

Actually there is not a space (see screenshot)

screenshot.png

data_null__
Jade | Level 19

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

massi
Calcite | Level 5

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

massi
Calcite | Level 5

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:



massi
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 19 replies
  • 6908 views
  • 0 likes
  • 8 in conversation