Impoting EXCEL file using a library from a server.

[kmardinian]

I am trying to upload an excel file that is located in the Input folder on a server. For some reason though, every time I try to import it, it remaps the path to the Prog folder instead. Any help is greatly appreciated. Thank you!

91   %let input1=&root\data\Input;
92   %put input1=&input1;
input1=\\SERVER.XX.COM\E\PROJECT WORK\FULL SERVICE\YY\STAT\data\Input
93   libname input1 "&input1";
NOTE: Libref INPUT1 was successfully assigned as follows:
      Engine:        V9
      Physical Name: \\SERVER.XX.COM\E\PROJECT WORK\FULL SERVICE\YY\STAT\data\Input

94   proc import out=input1.PP datafile='E\Project Work\Full Service\YY\STAT\Blinded\TFL\Data\Input\PP.xlsx' dbms=xlsx replace;
95       getnames=yes;
96   run;

ERROR: Physical file does not exist, \\server.xx.com\E\Project Work\Full Service\YY\STAT\Prog\E\Project Work\Full Service\YY\STAT\Data\Input\PP.xlsx.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.17 seconds
      cpu time            0.00 seconds

[Kurt_Bremser]

Since you gave a relative path for the datafile, SAS started to look in the current working directory of the SAS process, which seems to be \\server.xx.com\E\Project Work\Full Service\YY\STAT\Prog

[ballardw]

If the file you are referencing is supposed to be on YOUR E drive then perhaps instead of

proc import out=input1.PP datafile='E\Project Work\Full Service\YY\STAT\Blinded\TFL\Data\Input\PP.xlsx' 

you want

proc import out=input1.PP datafile='E:\Project Work\Full Service\YY\STAT\Blinded\TFL\Data\Input\PP.xlsx' 

If you are working in a server environment the path to the XLSX file has to be from the server perspective and it may not have a drive E.

If you aren't working with a SAS server then I would navigate to the location in Windows Explorer or similar drive navigation tool and ensure that the complete path is as you have. In windows explorer I usually click on the "path" section and copy/paste into the SAS editor to get the exact path seen from my computer.

[kmardinian]

Hi, thanks for your help! So the server does have the E drive in it. I tried running it with the code you showed me, but it still doesn't seem to be finding the file even though now the path is correct.

78   libname input1 "&root\data\Input";
NOTE: Libref INPUT1 was successfully assigned as follows:
      Engine:        V9
      Physical Name: \\SERVER.XX.COM\E\PROJECT WORK\FULL SERVICE\YY\STAT\BLINDED\TFL\data\Input
79
80
81   proc import out=input1.PP datafile='E:\Project Work\Full Service\YY\STAT\Blinded\TFL\Data\Input\PP.xlsx' dbms=xlsx replace;
82       getnames=yes;
83   run;

ERROR: Physical file does not exist, E:\Project Work\Full Service\YY\STAT\Blinded\TFL\Data\Input\PP.xlsx.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.08 seconds
      cpu time            0.01 seconds

[Kurt_Bremser]

When SAS tells you the file is not there, then it is not there, period.

Log on to the server and navigate to that path, and inspect its contents.

[kmardinian]

The file is there, I have checked multiple times. I am just not sure whether SAS knows to look for an excel file.

[Kurt_Bremser]

Read my post again. IT'S.NOT.THERE. At least not where SAS is looking. Keep in mind that a local path on your desktop is most probably invisible to the server.

[kmardinian]

The line of code works perfectly fine for others that are connected to the server the same way that I am.

[Kurt_Bremser]

---

@kmardinian wrote:

The line of code works perfectly fine for others that are connected to the server the same way that I am.

---

Then look at the differences between the users involved. There is one. Different permissions, different login scripts, network shares not or differently mounted, ...

When SAS says the file ain't there, it ain't there (for the SAS process to access). What I tell you three times is the truth.

[Tom · Accepted solution]

So your first step shows that this directory exists:

 \\SERVER.XX.COM\E\PROJECT WORK\FULL SERVICE\YY\STAT\BLINDED\TFL\data\Input

Your second step shows that this file does NOT exist.

                E:\Project Work\Full Service\YY\STAT\Blinded\TFL\Data\Input\PP.xlsx

Can you see the differences?

Assuming that case doesn't matter you could try:

datafile="&root\data\Input\PP.xlsx"