BookmarkSubscribeRSS Feed
ThunderOne
Calcite | Level 5

Unable to import an excel file through EG... here is the code (removed various parts for security).  

 

The log shows an additional slash being added... not sure how to fix (SAS EG 7.13):

 

%Let Upload = /sasdata/users/jondoe1/;
%Let Path_In = \\Client\S$S:\DA_work_files\filename.xlsx;


/*Run Copy Task to Copy to Out network*/

Proc Import Out=jondoe1_Lib.list

Datafile='//sasdata/users/jondoe1/filename.xlsx'

dbms=xlsx replace;

Sheet='List';

Run;

 

 

The macros run fine. 

 

Log:

ERROR: Physical file does not exist, //sasdata/users/jondoe1//filename.xlsx.

9 REPLIES 9
Reeza
Super User

The extra slashes aren't the issue. Is your XLSX file on the server?

ThunderOne
Calcite | Level 5

I am not sure, as this is a colleague's work.  I was assuming the first statement would bring the file into the sasusers folder.  I ended up using the import wizard and importing the file directly, rather than making it two steps.  That worked.


Thank you for your help.

Reeza
Super User

Yeah, I think you're running into the 'server' vs 'desktop' issue. the file is on your local drive and you can't access it until its on the server. The GUI doesn't have the same restriction. 

 

Yes, this is super annoying.

Patrick
Opal | Level 21

@Reeza

Actually the two slashes at the beginning of the path indicate that this is a UNC path so depending on the site specific topology such a path could work from both client and server side.

//sasdata/users

 

Not sure why you believe the two slashes in the middle of the pathname aren't an issue. I believe they are.

 //sasdata/users/jondoe1//filename.xlsx

 

@ThunderOne

The code you've posted doesn't show it but eventually remove the last slash in below command line.

%Let Upload = /sasdata/users/jondoe1/;

Reeza
Super User

@Patrick I don't know why it's not an issue, but I know that it is. SAS frequently does this and if you include 2 it works fine as well. See code and log below.

 

I *think* it might be the local vs server issue because in the middle of the path is S:\ -> Looks like a drive mapping, but I could definitely be wrong there. 

  

%Let Upload = /sasdata/users/jondoe1/;
%Let Path_In = \\Client\S$S:\DA_work_files\filename.xlsx;

  

Code:

 

proc import out=want datafile='/folders/myfolders//output.csv' dbms=csv replace;
run;
 
60 /**********************************************************************
61 * PRODUCT: SAS
62 * VERSION: 9.4
63 * CREATOR: External File Interface
64 * DATE: 24AUG17
65 * DESC: Generated SAS Datastep Code
66 * TEMPLATE SOURCE: (None Specified.)
67 ***********************************************************************/
68 data WORK.WANT ;
69 %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
70 infile '/folders/myfolders//output.csv' delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;
71 informat _____Alfred $7. ;
72 informat M $1. ;
73 informat _14 best32. ;
74 informat VAR4 $1. ;
75 informat _112_5 best32. ;
76 informat VAR6 $1. ;
77 informat _69 best32. ;
78 format _____Alfred $7. ;
79 format M $1. ;
80 format _14 best12. ;
81 format VAR4 $1. ;
82 format _112_5 best12. ;
83 format VAR6 $1. ;
84 format _69 best12. ;
85 input
86 _____Alfred $
87 M $
88 _14
89 VAR4 $
90 _112_5
91 VAR6 $
92 _69
93 ;
94 if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
95 run;
 
NOTE: The infile '/folders/myfolders//output.csv' is:
Filename=/folders/myfolders//output.csv,
Owner Name=UNKNOWN,Group Name=games,
Access Permission=-rw-r--r--,
Last Modified=June 21, 2017 21:27:47 o'clock,
File Size (bytes)=1440
 
NOTE: 18 records were read from the infile '/folders/myfolders//output.csv'.
The minimum record length was 73.
The maximum record length was 77.
NOTE: The data set WORK.WANT has 18 observations and 7 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
 
DazyParker
Calcite | Level 5

ERROR: Physical file does not exist.

 

Reasons:

 

  • Path of the file is mispelled
  • Directory and subdirectory is missing
  • Permission issue
  • Server issue
  • You do not follow the nomenclature of the file name
  • drive is not mapped into correct location
Bilbottom
Calcite | Level 5

I just encountered this double-slash issue myself, and the root of the problem was that the file to import was open. Once I closed the file, the proc import worked fine.

DGBK
Obsidian | Level 7
Thank you. I was having a similar problem today. I did not get an error that the file was not imported, but I was getting the double slash. This should be listed as the answer.
Tom
Super User Tom
Super User

The extra / in the error message is a BUG in the error report code of PROC IMPORT.  It does that for XLSX engine, but not for the CSV engine.

Example:

proc import datafile='~/nosuchfile' out=want dbms=xlsx;
run;
proc import datafile='~/nosuchfile' out=want dbms=csv;
run;

Results:

 69         proc import datafile='~/nosuchfile' out=want dbms=xlsx;
 70         run;
 
 ERROR: Physical file does not exist, /home/user1//nosuchfile.


  
 71         proc import datafile='~/nosuchfile' out=want dbms=csv;
 72         run;
 
 ERROR: Physical file does not exist, /home/user1/nosuchfile.        
 

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 9 replies
  • 4994 views
  • 4 likes
  • 7 in conversation