topic Re: ERROR: Physical file does not exist ? in SAS Programming

ERROR: Physical file does not exist ?

leehsin — Wed, 15 May 2019 19:15:29 GMT

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.

Re: ERROR: Physical file does not exist ?

Reeza — Wed, 15 May 2019 19:35:34 GMT

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?

Re: ERROR: Physical file does not exist ?

leehsin — Wed, 15 May 2019 19:41:47 GMT

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.

Re: ERROR: Physical file does not exist ?

Reeza — Wed, 15 May 2019 20:44:23 GMT

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.

Re: ERROR: Physical file does not exist ?

leehsin — Wed, 15 May 2019 21:17:17 GMT

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.

Re: ERROR: Physical file does not exist ?

Tom — Thu, 16 May 2019 01:50:50 GMT

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.

Re: ERROR: Physical file does not exist ?

leehsin — Thu, 16 May 2019 02:04:03 GMT

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.

Re: ERROR: Physical file does not exist ?

Tom — Thu, 16 May 2019 03:20:37 GMT

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.

Re: ERROR: Physical file does not exist ?

Patrick — Thu, 16 May 2019 03:25:26 GMT

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

Re: ERROR: Physical file does not exist ?

leehsin — Thu, 16 May 2019 04:05:49 GMT

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

Re: ERROR: Physical file does not exist ?

Reeza — Thu, 16 May 2019 14:51:13 GMT

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

Re: ERROR: Physical file does not exist ?

leehsin — Thu, 16 May 2019 15:22:39 GMT

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

Re: ERROR: Physical file does not exist ?

Reeza — Thu, 16 May 2019 15:58:40 GMT

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.

Re: ERROR: Physical file does not exist ?

DWilson — Thu, 16 May 2019 18:19:45 GMT

In your first post you have the string "bene" in your file path. In all subsequent posts the "bene" is gone and replaced with "be"

Which one is correct? "bene" or "be"?

Also, the let statement appears to have a space between the equals sign and the start of the name you want. Maybe the space is being captured and inserted into your file path? On Linux, spaces in file names are possible but you need to use a / before the space. Maybe SAS is trying to do something like that and is not quite able to do it? Maybe SAS is translating the space as a / which would give you two slashes which means that the resulting file path would not be found.

Re: ERROR: Physical file does not exist ?

DWilson — Thu, 16 May 2019 18:53:16 GMT

In one of your posts you said this:

I defined DBMS= xlsx. &impl is resolved as " 2019".

so it does look like the let statement is capturing the leading space before 2019. Try removing the leading space in your let statement.

Re: ERROR: Physical file does not exist ?

leehsin — Fri, 17 May 2019 02:22:58 GMT

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

Re: ERROR: Physical file does not exist ?

leehsin — Fri, 17 May 2019 02:26:01 GMT

'bene' you caught is a typo. It should be 'be'. Thanks.

I will check the effect of the space between the equal sign and the start of text in let statement. Thanks for the suggestion.

Re: ERROR: Physical file does not exist ?

leehsin — Fri, 17 May 2019 15:29:33 GMT

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.

Re: ERROR: Physical file does not exist ?

leehsin — Mon, 20 May 2019 13:38:40 GMT

Thanks to all suggestions in the discussions. These suggestions provide all the ways/directions to debug the code. In summary, by testing, I found in the current scenario, two ways made it work:
1) update the cases of letters in the path/file to be consistent;
2) I added a slash to the end of the first line, it could import the data (not knowing why, but this was what happened):
%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.)