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
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.
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
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?
Interesting. Are there any other unexpected notes in the log? Anything? The excel-file is closed, when trying to read it?
-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.
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?
@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.
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.
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.