PROC IMPORT with FILENAME statement

[JohnT]

Hi,

I'm trying to migrate from using PC SAS on a desktop, to Enterprise Guide connecting to a server.  The server is 64bit, the client is EG 4.3 32bit.

It was supposed to run our old SAS jobs in the same way.

Right now, I'm having lots of problems importing/exporting Excel files.

We use Excel 2007 mostly, however there are also some older Excel 2003 files sitting around.

20 FILENAME rf "s:\sasuserdata\ref\IBNR_EPA_Parameters.xls";
21 
　
22 proc import
23 datafile = rf
24 out = IBNR_Factor2
25 dbms = excelcs replace;
26 sheet = "IBNR_Factor";
27 run; 
ERROR: Unable to open file RF. It does not exist or it is already opened exclusively by another 
user, or you need permission to view its data.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.06 seconds
cpu time 0.01 seconds
28 
29 
　
30 proc import
31 datafile = "s:\sasuserdata\ref\IBNR_EPA_Parameters.xls"
32 out = IBNR_Factor
33 dbms = excelcs replace;
34 sheet = "IBNR_Factor";
35 run;
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.20 seconds
cpu time 0.04 seconds
NOTE: WORK.IBNR_FACTOR data set was successfully created.

A lot of our code uses the FILENAME statement, does this mean I cannot use it anymore, or do I need to copy the string into the PROC IMPORT datastep?  I've already changed the EXCEL DBMS to EXCELCS and am prepared to change this in all our jobs.

Thanks.

[newbie]

SAS EG runs on a remote server thats why when you try to import a file availble on windows, you are getting this error. This can work if you make those excel files available on the remote sas server.

[art297]

Just out of curiosity of whether it may be the lack of a pc files' server issue, will the file(s) import if you change the DBMS to xls?

[JohnT]

newbie, the folder has been mapped on the remote server too.  And the second import from the log worked, it's just when I use the FILENAME statement it doesn't.

art297, when I change the DBMS to XLS is works!  So it's a start, and I might have provided a bad example

Here's the output with art297's suggestion:

19 FILENAME rf "s:\sasuserdata\ref\IBNR_EPA_Parameters.xls";
20 
21 proc import
22 datafile = rf
23 out = IBNR_Factor2
24 dbms = xls replace;
25 sheet = "IBNR_Factor";
26 run;
NOTE: The import data set has 47 observations and 42 variables.
NOTE: Compressing data set WORK.IBNR_FACTOR2 increased size by 100.00 percent. 
Compressed is 2 pages; un-compressed would require 1 pages.
NOTE: WORK.IBNR_FACTOR2 data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds
27 
28 proc import
29 datafile = "s:\sasuserdata\ref\IBNR_EPA_Parameters.xls"
30 out = IBNR_Factor
31 dbms = xls replace;
32 sheet = "IBNR_Factor";
33 run;
NOTE: The import data set has 47 observations and 42 variables.
NOTE: Compressing data set WORK.IBNR_FACTOR increased size by 100.00 percent. 
Compressed is 2 pages; un-compressed would require 1 pages.
NOTE: WORK.IBNR_FACTOR data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

^^
They both work

I don't think this solution will work for Excel 2007/2010 (xlsx) filles though.

Importing the same file converted to XLSX:

19 FILENAME rf "s:\sasuserdata\ref\IBNR_EPA_Parameters.xlsx";
20 
21 proc import
22 datafile = rf
23 out = IBNR_Factor2
24 dbms = xls replace;
25 sheet = "IBNR_Factor";
26 run;
Spreadsheet isn't from Excel V5 or later. Please open it in Excel and Save as V5 or later
Requested Input File Is Invalid
ERROR: Import unsuccessful. See SAS Log for details.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.01 seconds
cpu time 0.03 seconds
27 
　
28 proc import
29 datafile = "s:\sasuserdata\ref\IBNR_EPA_Parameters.xlsx"
30 out = IBNR_Factor
31 dbms = xls replace;
32 sheet = "IBNR_Factor";
33 run;
Spreadsheet isn't from Excel V5 or later. Please open it in Excel and Save as V5 or later
Requested Input File Is Invalid
ERROR: Import unsuccessful. See SAS Log for details.
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.00 seconds

^^

Both versions fail

I have been told that that the port the PC file server is listening for is 9621 so I have also tried the server/port options:

19 FILENAME rf "s:\sasuserdata\ref\IBNR_EPA_Parameters.xlsx";
20 
21 proc import
22 datafile = rf
23 out = IBNR_Factor2
24 dbms = excelcs replace;
25 sheet = "IBNR_Factor";
26 server = "sascomp01.calliden.com.au";
27 port = 9621;
28 run;
ERROR: Unable to open file RF. It does not exist or it is already opened exclusively by another 
user, or you need permission to view its data.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.07 seconds
cpu time 0.04 seconds
29 
　
30 proc import
31 datafile = "s:\sasuserdata\ref\IBNR_EPA_Parameters.xlsx"
32 out = IBNR_Factor
33 dbms = excelcs replace;
34 sheet = "IBNR_Factor";
35 server = "sascomp01.calliden.com.au";
36 port = 9621;
37 run;
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.29 seconds
cpu time 0.04 seconds
NOTE: WORK.IBNR_FACTOR data set was successfully created.

^^

So it looks as though I can't use the FILENAME statement with PROC IMPORT for XLSX files?

[art297]

Methinks it's not that you can't use the Filename statement, but more of a server issue.  Do the xlsx files reside on the server.  I haven't read as much as I'd like on the topic but, from my understanding, that is a requirement.  I suggested the xls approach to see if you could read the original files.

[JohnT]

Where on the server do the files need to belong for it to be classified as on the server ?

The Excel file does reside on the server's S: drive.

This is not a mapped network drive.

But if there is a better location for me to test this, I can try, I don't think I can write to the C: drive though.