ERROR: Physical file does not exist ?

[leehsin]

Hi, I had a puzzle as below:

I have a excel (file name: caCat_Test_Plan_2019q1.xlsx) in the fold: 

&rootDir.projects/pmh/qa/qci/pmh-bene-car-cat/2019q1

I use this macro to import the excel file:

%macro CsvExl_Import(location=, filename=, DBMS=, outfile=);
proc import datafile="&location./&filename..&DBMS."
DBMS= &DBMS. 
REPLACE
out= work.&outfile.;
getnames=yes;
run;
%mend CsvExl_Import;

The code for importing the Excel file:

%let impl = 2019q1;

%CsvExl_Import(location= &rootDir.projects/pmh/qa/qci/pmh-be-car-cat/&impl.
              ,filename= caCat_Test_Plan_&impl.
              ,DBMS= xlsx
              ,outfile= caCat_Test_Plan_&impl.)

&rootDir. was defined at the beginning. Here is the log with one error:

MPRINT(CSVEXL_IMPORT): proc import datafile="/u02/users/xlis/sas-fork/projects/pmh/qa/qci/pmh-be-car-cat/2019q1caCat_Test_Plan_2019q1.xlsx" DBMS= xlsx REPLACE out=
work.caCat_Test_Plan_2019q1;
MPRINT(CSVEXL_IMPORT): RXLX;
MPRINT(CSVEXL_IMPORT): getnames=yes;
MPRINT(CSVEXL_IMPORT): run;

ERROR: Physical file does not exist, /u02/users/xlis/sas-fork/projects/pmh/qa/qci/pmh-be-car-cat//2019q1caCat_Test_Plan_2019q1.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

I couldn't figure out the reason of having double slash before 2019 and the missing slash before the Excel file name, and causing the failure of importing. Asking for help to fix the problem.

Thanks.

[Reeza]

What happens if you hard code things instead of using macro variables. Note that one line in the log is RXLX - where does that come from?

[leehsin]

This is part of my code, and the problem does not make sense to me. I used 2019q1 instead of the &impl., it came out the same error.

[Reeza]

Then the filepath is wrong. Make sure you macro variables are resolving to what you think they should be, but if it is, SAS may not have access to that folder/drive or the path may be different.

[leehsin]

There is no mistake for the file path. I resolved it by adding a slash after the macro variable &impl. for the location:

%CsvExl_Import(location= &rootDir.projects/pmh/qa/qci/pmh-be-car-cat/&impl./
              ,filename= caCat_Test_Plan_&impl.
              ,DBMS= xlsx
              ,outfile= caCat_Test_Plan_&impl.)

But I still could not explain it because the slash '/' added seems an extra one. 

Similarly, if I had one more macro variables at the end like: 

 location= &rootDir.projects/pmh/qa/qci/pmh-be-car-cat/&impl./&evn. for another path which is:

&rootDir.projects/pmh/qa/qci/pmh-be-car-cat/2019q1/dev

The error will come out as 3 slashs instead of 2 slashes. In this case, that I add an extra slash like above example can not fix the problem. 

[Tom]

I don't see any extra slashes in any of the error messages you have posted. Although it is hard to read them if you don't use the Insert Code pop-up so that the formatting is preserved.

[leehsin]

In my first post, the error message is:

ERROR: Physical file does not exist, /u02/users/xlis/sas-fork/projects/pmh/qa/qci/pmh-be-car-cat//2019q1caCat_Test_Plan_2019q1.

There are two slashes between "pmh-be-car-cat" and "2019q1caCat_Test_Plan_2019q1". This error caused the failure of importing the data.

The right file path I expect the code in my first post to resolve is "/u02/users/xlis/sas-fork/projects/pmh/qa/qci/pmh-be-car-cat/2019q1/caCat_Test_Plan_2019q1", but the actual result is an error. I can't find out the reason.

[Tom]

One possible cause of the extra / would be that the %LET in your code did not run and instead the value of the IMPL macro variable included a leading slash.  Perhaps the statement before what you posted was missing an ending semi-colon?

The other might be that PROC IMPORT got confused by such a long path?

Do you need to use PROC IMPORT?  Why not just use the XLSX libname engine instead?

Of course none of this matters if the real issue is that the file is not where you think it is and that is why SAS cannot find it.  Your paths are unix paths so make sure the case of every letter is correct as filenames on Unix are case sensitive.  Also make sure that there are not goofy characters in the actual filename, like spaces, that might be hard to see if you are trying to re-type the path instead of copying and pasting from your directory listing.

[Patrick]

I can't see the suffix .xlsx in the error message. Is that eventually the issue?

ERROR: Physical file does not exist, ....2019q1caCat_Test_Plan_2019q1.

Try:

&rootDir.projects/pmh/qa/qci/pmh-be-car-cat/&impl..xlsx

 Or eventually as a first test pass in the fully qualified path name like:

/u02/users/xlis/sas-fork/projects/pmh/qa/qci/pmh-be-car-cat/2019q1caCat_Test_Plan_2019q1.xlsx

[leehsin]

%macro CsvExl_Import(location=, filename=, DBMS=, outfile=);
proc import datafile="&location./&filename..&DBMS."
DBMS= &DBMS. 
REPLACE
out= work.&outfile.;
getnames=yes;
run;
%mend CsvExl_Import;

The suffix is added inside the macro code (line 2):  '&filename..&DBMS'.

[Reeza]

No it isn't. That's the DBMS. The extension needs to be on the file name as well.

[leehsin]

%CsvExl_Import(location= &rootDir.projects/pmh/qa/qci/pmh-be-car-cat/&impl./
              ,filename= caCat_Test_Plan_&impl.
              ,DBMS= xlsx
              ,outfile= caCat_Test_Plan_&impl.)

I defined DBMS= xlsx. &impl is resolved as " 2019". So, &filename..&DBMS. is resolved as "cacat_Test_plan_2019.xlsx". This name is the targeted file to import.

[Reeza]

Thanks, you are correct. You do need to test the hard coded version first - no macro variables at all. Does that work? If not, then it's a file path/access issue. If the hard coding does work then the issue is with the macro variable resolution most likely.

Other than that, your code is conceptually correct so I'm going to guess that it's a filepath/access issue.

[leehsin]

Good suggestion! I will test the hard coded version to diagnose the cause of the error.

[leehsin]

Reeza,

I restarted the SAS EG and re-selected all the programs I want to use, and re-run the codes line by line. I realized that the problem came from the environment before was messed up by various modified versions existed in the environment during the testing. The most critical problem was related to the filepath/access issue as you pointed out in your post. I have several letters which were upper cases in my code, but they were lower cases in the actual directory name (from the error message we can't see it). The double slashes '//' I got in the error message means the access denied because the path is case sensitive.