SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

Open excel with SAS in Read only mode

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 144
Accepted Solution

Open excel with SAS in Read only mode

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


Accepted Solutions
Solution
‎02-10-2017 08:48 AM
Super User
Posts: 7,447

Re: Open excel with SAS in Read only mode

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Solution
‎02-10-2017 08:48 AM
Super User
Posts: 7,447

Re: Open excel with SAS in Read only mode

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,720

Re: Open excel with SAS in Read only mode

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.

Valued Guide
Posts: 505

Re: Open excel with SAS in Read only mode

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
Valued Guide
Posts: 505

Re: Open excel with SAS in Read only mode

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 701 views
  • 1 like
  • 4 in conversation