Desktop productivity for business analysts and programmers

PROC IMPORT with FILENAME statement

Reply
Frequent Contributor
Posts: 75

PROC IMPORT with FILENAME statement

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.

Occasional Contributor
Posts: 11

PROC IMPORT with FILENAME statement

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.

Esteemed Advisor
Posts: 7,297

PROC IMPORT with FILENAME statement

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?

Frequent Contributor
Posts: 75

Re: PROC IMPORT with FILENAME statement

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?

Esteemed Advisor
Posts: 7,297

Re: PROC IMPORT with FILENAME statement

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.

Frequent Contributor
Posts: 75

Re: PROC IMPORT with FILENAME statement

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.

Ask a Question
Discussion stats
  • 5 replies
  • 8577 views
  • 0 likes
  • 3 in conversation