Desktop productivity for business analysts and programmers

Not able to export XLSM files through SAS EG

Reply
Occasional Contributor
Posts: 14

Not able to export XLSM files through SAS EG


Hi Team,

we are not able export xlsm file through SAS EG using PC file server. can you please advice how to proceed.

Here is the error message we are getting:

ERROR: CLI error trying to establish connection: [Microsoft][ODBC Excel Driver] Cannot update. Database or object is read-only.

ERROR: Error in the LIBNAME statement.

Current Environment: Linux x64 , EG 6.1

Do we need to Install Excel ODBC Drivers on our Linux box please advice how to resolve this issue.



Grand Advisor
Posts: 17,411

Re: Not able to export XLSM files through SAS EG

If EG is only on the server you can't use libname method to export. You didn't say that's how you're trying to export, but I'm assuming based on the error.

You'll need to use proc export instead, try with DBMS=XLSX or DBMS=EXCELCS

Occasional Contributor
Posts: 14

Re: Not able to export XLSM files through SAS EG


Hi Reeza,

Thank for quick response,

I used proc export and here is sample program

Proc EXPORT DATA=sashelp.shoes

OUTFILE= "\\SAS\UserData\newxlsm.xlsm

DBMS=EXCELCS  REPLACE;

PORT=9621;

SERVER= 'T01LPS2021.ent.rt.com';

sheet="xlsm";

RUN;

See the below error:

1. if I give DBMS=xlsx

ERROR is: ERROR 180-322: Statement is not valid or it is used out of proper order.

If I am giving DBMS=EXCELCS Error message is

ERROR: CLI error trying to establish connection: [Microsoft][ODBC Excel Driver] Cannot update. Database or object is read-only.

ERROR: Error in the LIBNAME statement.

Grand Advisor
Posts: 17,411

Re: Not able to export XLSM files through SAS EG

Do you have a valid license to SAS Access to PC Files?

You can check this by running

proc stint;run; *Checks license;

proc product_status;run; *checks installation;

Occasional Contributor
Posts: 14

Re: Not able to export XLSM files through SAS EG

Hi Team,

Yes we do have valid licens and currently using PC SAS 9.2 our users are able to create XLSM , XLSX file but in EG we are not able to creat same. I just wanted to know specific reason for it.

PC file server installed on Window's box. PC file server works fine for us to create MDB and normal excel file. But its not support for XLSX adn XLSM files.


Esteemed Advisor
Esteemed Advisor
Posts: 7,249

Re: Not able to export XLSM files through SAS EG

Just to check, I note that your exporting data to XSLX output destination, however appear to be talking about an XLSM file.  These are very subtley different file types.  XLSX is a zip file containing XML documents which reflect the data within the sheets.  XLSM are macro enabled (possibly with compiled VBA macro module), so I would not understand why you would want to create such as file from SAS.  The export to XLSX from SAS actually doesn't create an Excel XLSX file (unless your on the cutting edge of SAS releases), but a plain XML document which can be opened and interpreted by Excel.

So could you clarify what you are attempting to achieve.  Also, try using:
ods tagsets.excelxp file="abc.xls";

proc report data=...;

ods tagsets.excelxp close;

So the point is to create an output Excel file for review, as Excel is not a data transport format.  If you wanted to export to Excel to transfer data, then export to CSV or other delimited file.

Grand Advisor
Posts: 17,411

Re: Not able to export XLSM files through SAS EG

RW9 wrote:

The export to XLSX from SAS actually doesn't create an Excel XLSX file (unless your on the cutting edge of SAS releases), but a plain XML documewhich can be opened and interpreted by Excel.

I think thats only with regards to tagsets, not proc export.

Esteemed Advisor
Esteemed Advisor
Posts: 7,249

Re: Not able to export XLSM files through SAS EG

Yes, so long since used proc export.  You are quite correct. With the proc export dbms=xlsx it does create a zipped Open Office document.  Still not sure why he is trying to create XLSM though which are macro enabled zipped Open Office documents.

N/A
Posts: 1

Re: Not able to export XLSM files through SAS EG

XLSM are macro enabled (possibly with compiled VBA macro module), so I would not understand why you would want to create such as file from SAS.

I have a similar requirement. I do the heavy lifting in SAS and write the output of the process into an Excel (.xls) file where it drives a pivot table that in turn drives a bunch of dynamic data tables, which uses a custom VBA function to format the appearance of data values in cells. Recently, the output had more rows than a .xls file can handle, so I recreated the .xls in a .xlsx, which whinged about the VBA, so I used a .xlsm. When I attempted to write directory to the .xlsm from SAS using PROC EXPORT, I didn't have a lot of success. As a work around, I modified my pivot tables to externally reference a .xlsx that I create using SAS. It's a bit clunky because it means I have to have two files where I once had one (i.e. the .xlsx with the data and the .xlsm that does what I need it to do), but it's better than not being able to have my dynamic tables.

So, that's why I would like to be able to directly write into a .xlsm.

Grand Advisor
Posts: 17,411

Re: Not able to export XLSM files through SAS EG

For the record, you can write export directly to an XLSM - but I think you need SAS 9.3+.

Using Windows 7 and SAS 9.3 I use the libname method with out issues.

New Contributor
Posts: 2

Re: Not able to export XLSM files through SAS EG

Reeza, do you happen to have sample code using the libname statement you could share that write exports direclty to an XLSM file? If so, it would be greatly appreciated.

Esteemed Advisor
Posts: 5,198

Re: Not able to export XLSM files through SAS EG

Make sure that your Excel file is not locked in any way, and that is not write protected.

45835 - A CLI error occurs when you try to establish a connection while importing Excel files with t...

Data never sleeps
Valued Guide
Posts: 2,111

Re: Not able to export XLSM files through SAS EG

Reddy,

You may want to repost this to the Enterprise Guide forum.  A lot of the responses you have gotten refer to writing code rather than the EGuide export task (and it is unclear which you used originally).  Also, you said you are using a PC Files server, but the error message indicates the server is on a Linux box.

Valued Guide
Posts: 3,206

Re: Not able to export XLSM files through SAS EG

It is not SAS EG you should mention.

The port number 9621 and SERVER= 'T01LPS2021.ent.rt.com'   are that PC-files server on a Windows box.  That should be installed in 32 or 62 bit version but needing the same bitness office drivers (ACE) on that windows machine being installed and there should be an authentication part being active. That code is just normal SAS-Base could could be run with any kind of tool not only Eguide.

Saying this and you have problems with that concept it could be the deployment is not validated / not functional working. Who has done that installation and who is helping you?

---->-- ja karman --<-----
Ask a Question
Discussion stats
  • 13 replies
  • 2921 views
  • 0 likes
  • 8 in conversation