BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
arodriguez
Lapis Lazuli | Level 10

Hi everyone,

 

I'm working with an excel file on a shared directory. Some times I'm getting errors because someone has the document opened in edit mode, and SAS can not connect to the excel file.

 

Is is possible to connect to a excel file with read only mode? If there is a way this should stop producing connection errors.

 

Thanks in advance 

  Toni

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

The problem is that the execrable Excel keeps the file open on the operating system level, and that interferes with some open modes that include read access (ie setting a libname xlsx automatically enables you to create sheets - new datasets - and that infers permament write access).

One of the reasons Excel files (xls, xlsx and their brethren) are considered bad choices for data interchange. Text-based formats (csv) are preferred.

View solution in original post

5 REPLIES 5
Kurt_Bremser
Super User

The problem is that the execrable Excel keeps the file open on the operating system level, and that interferes with some open modes that include read access (ie setting a libname xlsx automatically enables you to create sheets - new datasets - and that infers permament write access).

One of the reasons Excel files (xls, xlsx and their brethren) are considered bad choices for data interchange. Text-based formats (csv) are preferred.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Ii is possible to copy the workbook whilst it is open, so can you not do copy command to your local work area and use the copy, then it can either be removed by you, or at close down when work is removed.

rogerjdeangelis
Barite | Level 11
1205  libname xel "d:/xls/class.xlsx" access=readonly;
NOTE: Libref XEL was successfully assigned as follows:
      Engine:        EXCEL
      Physical Name: d:/xls/class.xlsx
1206  data xel.write;
1207    set sashelp.class;
1208  run;

ERROR: Write access to member XEL.write.DATA is denied.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              369.81k
      OS Memory           21484.00k
      Timestamp           02/10/2017 11:19:00 AM
      Step Count                        190  Switch Count  0
rogerjdeangelis
Barite | Level 11

As a side note.

 

SOAPBOX ON

It appears that users of this list seem to favor,  'proc import' and 'proc export'. I know SAS seems more interested in expanding functionality of these procedures and not  adding the same functionality to 'libname' and passthu 'connections', but I don't think 'proc import and proc export' have passed the libname and connection  engines functionality  yet.

 

I prefer libname, and if SAS 'deprecates' the libname and 'connection engines, then I will just turn to R, Python and Perl.

 

SOAPBOX OFF

SASUser19999
Calcite | Level 5

Late to the party!

 

This is because an invisible warning box of 'File is already open by other users. You'll have to open it as read-only...' comes first. Such a warning box prevents SAS from launching the Excel Engine. To solve it, simple do the following:

 

1) Run the MS Excel application in your local computer. You do not have to open any excel file, not even the file contains data to be imported. 

2)Run your SAS Proc Import program.

 

You'll see that indeed SAS opens the source excel file in the EXCEL application, and import data into the target data set.

 

The only trouble is that you'll have to close the excel file and the EXCEL application by yourself. It sounds like after importing from the source file, SAS does not even bother to close it. Man Happy 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Discussion stats
  • 5 replies
  • 8307 views
  • 2 likes
  • 5 in conversation