topic Open excel with SAS in Read only mode in Microsoft Integration with SAS

Open excel with SAS in Read only mode

arodriguez — Fri, 10 Feb 2017 13:01:51 GMT

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

Re: Open excel with SAS in Read only mode

Kurt_Bremser — Fri, 10 Feb 2017 13:09:45 GMT

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.

Re: Open excel with SAS in Read only mode

RW9 — Fri, 10 Feb 2017 13:13:49 GMT

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.

Re: Open excel with SAS in Read only mode

rogerjdeangelis — Fri, 10 Feb 2017 16:19:54 GMT

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

Re: Open excel with SAS in Read only mode

rogerjdeangelis — Fri, 10 Feb 2017 16:26:58 GMT

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

Re: Open excel with SAS in Read only mode

SASUser19999 — Thu, 05 Apr 2018 15:33:08 GMT

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.