topic Re: PROC IMPORT with FILENAME statement in SAS Enterprise Guide

PROC IMPORT with FILENAME statement

JohnT — Fri, 07 Oct 2011 04:37:29 GMT

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.

PROC IMPORT with FILENAME statement

newbie — Fri, 07 Oct 2011 18:26:15 GMT

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.

PROC IMPORT with FILENAME statement

art297 — Fri, 07 Oct 2011 18:37:04 GMT

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?

Re: PROC IMPORT with FILENAME statement

JohnT — Sun, 09 Oct 2011 22:42:35 GMT

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";

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

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?

Re: PROC IMPORT with FILENAME statement

art297 — Sun, 09 Oct 2011 23:19:37 GMT

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.

Re: PROC IMPORT with FILENAME statement

JohnT — Mon, 10 Oct 2011 00:02:13 GMT

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.