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?
