topic Impoting EXCEL file using a library from a server. in SAS Programming

Impoting EXCEL file using a library from a server.

kmardinian — Thu, 20 Sep 2018 17:09:16 GMT

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

Re: Impoting EXCEL file using a library from a server.

Kurt_Bremser — Thu, 20 Sep 2018 17:15:29 GMT

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

Re: Impoting EXCEL file using a library from a server.

ballardw — Thu, 20 Sep 2018 17:20:11 GMT

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.

Re: Impoting EXCEL file using a library from a server.

kmardinian — Thu, 20 Sep 2018 20:54:05 GMT

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

Re: Impoting EXCEL file using a library from a server.

Kurt_Bremser — Fri, 21 Sep 2018 05:03:32 GMT

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.

Re: Impoting EXCEL file using a library from a server.

kmardinian — Fri, 21 Sep 2018 15:05:11 GMT

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

Re: Impoting EXCEL file using a library from a server.

Kurt_Bremser — Fri, 21 Sep 2018 17:20:31 GMT

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.

Re: Impoting EXCEL file using a library from a server.

kmardinian — Fri, 21 Sep 2018 18:02:19 GMT

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

Re: Impoting EXCEL file using a library from a server.

Kurt_Bremser — Fri, 21 Sep 2018 18:32:21 GMT

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

Re: Impoting EXCEL file using a library from a server.

Tom — Fri, 21 Sep 2018 19:03:59 GMT

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"