topic Re: Importing an excel and getting unexpected "ERROR: Physical file does not exist" in New SAS User

Importing an excel and getting unexpected "ERROR: Physical file does not exist"

cieffegi — Fri, 02 Jul 2021 10:32:44 GMT

Hallo, i'm trying to import an excel with proc import.
it ends with an error "Physical file does not exist"
here it is the code i use:

PROC IMPORT DATAFILE="/my_path/name_of_the_excel.xlsx"

OUT=&path_out..name_of_the_table

(RENAME= (A=TIP_FAM B=AREA_MET_N C=PERIF_AREA_MET_50000_E_PIU_AB_N D=ALTRI_COM_FINO_50000_N
E=AREA_MET_C F=PERIF_AREA_MET_50000_E_PIU_AB_C G=ALTRI_COM_FINO_50000_C
H=AREA_MET_S I=PERIF_AREA_MET_50000_E_PIU_AB_S J=ALTRI_COM_FINO_50000_S))

DBMS=XLSX

REPLACE;

SHEET='name_of_the_subsheet';

RANGE="A4:J41";

getnames=no;

RUN;

I've duble checked that the real name of "my_path" (which is a server folder) and name_of_the_excel are spelled correctly.
The excel is available in "my_path" and can be opened with excel app without any problem.
It is composed of multiple sub sheets of which i want to keep only the one named "name of the subsheet".

Thanks for any help

Re: Importing an excel and getting unexpected "ERROR: Physical file does not exist"

PaigeMiller — Fri, 02 Jul 2021 10:36:59 GMT

When SAS says the file doesn't exist, and the user says it does exist, I believe SAS.

Unfortunately, you haven't given us the real path and file name, so we can't help further. We would need the real path and file name in the code, and a screen capture of the same folder in your operating system.

Re: Importing an excel and getting unexpected "ERROR: Physical file does not exist"

cieffegi — Fri, 02 Jul 2021 10:47:07 GMT

this is the real code

PROC IMPORT DATAFILE="S:/CCR/Team MODELLI/99_richieste_spot/Poverta _assoluta_ISTAT.xlsx"

OUT=&LIB_OUT..ISTAT_SOGLIE_MENS_POV_ASSOLUTA

(RENAME= (A=TIP_FAM B=AREA_MET_N C=PERIF_AREA_MET_50000_E_PIU_AB_N D=ALTRI_COM_FINO_50000_N
E=AREA_MET_C F=PERIF_AREA_MET_50000_E_PIU_AB_C G=ALTRI_COM_FINO_50000_C
H=AREA_MET_S I=PERIF_AREA_MET_50000_E_PIU_AB_S J=ALTRI_COM_FINO_50000_S))

DBMS=XLSX

REPLACE;

SHEET='Prospetto_8_ok';

RANGE="A4:J41";

getnames=no;

RUN;

and here is the screen capture of the folder

Re: Importing an excel and getting unexpected "ERROR: Physical file does not exist"

andreas_lds — Fri, 02 Jul 2021 10:56:10 GMT

If sas says that a file doesn't exist, it either doesn't exist, or (more likely) the drive you are using is not visible for sas. So: is sas running locally or on a server?

Re: Importing an excel and getting unexpected "ERROR: Physical file does not exist"

cieffegi — Fri, 02 Jul 2021 11:01:23 GMT

on a server.
I have no issues importing txt or exporting whatever i want in that location.

But when it comes to importing excel i get that message.
Which is, frankly, annoing.

Re: Importing an excel and getting unexpected "ERROR: Physical file does not exist"

andreas_lds — Mon, 05 Jul 2021 04:37:19 GMT

Interesting. Are there any other unexpected notes in the log? Anything? The excel-file is closed, when trying to read it?

Re: Importing an excel and getting unexpected "ERROR: Physical file does not exist"

SASKiwi — Mon, 05 Jul 2021 06:53:57 GMT

Importing or exporting how? For example if you do this via the SAS Import / Export File wizard in SAS Enterprise Guide, that will happen on your PC which knows about the mapped S drive. Running the same process via PROC IMPORT or EXPORT in SAS code means it will run on the remote SAS App Server which may know nothing about your mapped S drive. You need to talk to your SAS administrator to agree on a folder location that is accessible to your remote SAS server.

Also I'm somewhat mystified by your use of a Windows driver letter mapping (S:) and the use of forward slashes after that which is how you do a Unix path.

Re: Importing an excel and getting unexpected "ERROR: Physical file does not exist"

cieffegi — Mon, 05 Jul 2021 06:56:14 GMT

-the excel file is colsed

-this is the complete log message:

ERROR: Physical file does not exist, /sascrediti/config_credit/Lev1/SASAppCredit/S:/ccr/Team
MODELLI/99_richieste_spot//Poverta _assoluta_ISTAT.
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.01 seconds

NOTE: Remote submit to SERV complete.

Re: Importing an excel and getting unexpected "ERROR: Physical file does not exist"

Patrick — Mon, 05 Jul 2021 07:01:34 GMT

According to the log you've posted the pathname appears to be truncated. There is also a double slash in the path which doesn't look right - or at least you forgot to add the xlsx suffix.

How do you pass the path to Proc Import?

Re: Importing an excel and getting unexpected "ERROR: Physical file does not exist"

andreas_lds — Mon, 05 Jul 2021 07:17:18 GMT

@cieffegi wrote:

-the excel file is colsed

-this is the complete log message:

ERROR: Physical file does not exist, /sascrediti/config_credit/Lev1/SASAppCredit/S:/ccr/Team
MODELLI/99_richieste_spot//Poverta _assoluta_ISTAT.

The red text shows, that your sas-server has no access to your s-drive.

Re: Importing an excel and getting unexpected "ERROR: Physical file does not exist"

cieffegi — Mon, 05 Jul 2021 07:57:35 GMT

But why, then, it correctly imports txt and exports txt or exel in the same location?

Re: Importing an excel and getting unexpected "ERROR: Physical file does not exist"

Kurt_Bremser — Mon, 05 Jul 2021 08:38:04 GMT

This

/sascrediti/config_credit/Lev1/SASAppCredit/S:/ccr/Team MODELLI/99_richieste_spot//Poverta _assoluta_ISTAT

tells us the most important thing: your SAS session runs on a remote UNIX server. The UNIX filesystem has no drive letters, it has a single directory tree starting at root (/). The Windows path you supplied does not start with a forward slash, so it is considered relative to the current working directory of your SAS session (the place from where it was started, which is /sascrediti/config_credit/Lev1/SASAppCredit/).

You either need to have your Windows S drive mounted on the UNIX filesystem (if that is the case, your SAS admin can tell you the UNIX path to it), or you need to first transfer the file to the server so you can read it there.

If importing other types of files was successful, post the log(s) from these imports, so we can point out the differences.

We could have come to these conclusions much earlier if you had posted the log. Hint for the future: ALWAYS post the SAS log when you have a problem. Use the </> button for posting logs.