topic Export into Excel as a shared file in SAS Enterprise Guide

Export into Excel as a shared file

mondaytea — Mon, 11 Feb 2019 04:59:57 GMT

Hi all,

Does anyone know if SAS has the capability to export an excel file that will have the shared option applied to it so that multiple users can open the same excel file and edit it.

I'm using SAS EG 7.12

The code I use to export is

proc export data = GCR.QA_21days_report_&LAST_FRI_STAMP. dbms=xlsx
outfile = "&output_path_21.\21_DAYS_REPORT_&LAST_FRI_STAMP..xlsx"
replace;
run;

Re: Export into Excel as a shared file

SASKiwi — Mon, 11 Feb 2019 07:03:01 GMT

Unfortunately not. This is an Excel limitation not a SAS one. Excel is after all a single-user tool. Try opening a spreadsheet that someone else or some other process has open and it automatically switches to read-only. No SAS involved.

Re: Export into Excel as a shared file

mondaytea — Mon, 11 Feb 2019 08:08:47 GMT

Thanks for your response however it's not an Excel limitation. If you look at my attached screenshot you can see that in Excel can share the file by doing the following steps.

Click on Review->Share Workbook->Tick the box next to 'Allow changes by more than one user at the same time. This also allows workbook merging'

Re: Export into Excel as a shared file

Patrick — Mon, 11 Feb 2019 11:07:24 GMT

@mondaytea

I don't expect SAS to be able to create such a workbook but I would expect that SAS doesn't change the properties on a pre-existing workbook when creating/replacing a sheet in the workbook. BUT... you must be sure that no one has the workbook open when SAS is trying to access it as this will most likely not work.

And what I found in the Excel help of my Excel version:

We've tried co-authoring with Excels in SharePoint. After people have lost their changes more than once we've stopped using this feature.

Re: Export into Excel as a shared file

SASKiwi — Mon, 11 Feb 2019 22:31:58 GMT

Don't see the screenshot but I've found the option in Excel 365. There is no way to set this up from SAS. You would have to set it up manually yourself.

I agree with @Patrick that this is unlikely to work reliably with SAS. If you want to have SAS users reliably updating the same tables at the same time use a database designed for transaction processing like SQL Server or Oracle.