Desktop productivity for business analysts and programmers

SAS EG and DDE

Accepted Solution Solved
Reply
Super Contributor
Posts: 256
Accepted Solution

SAS EG and DDE

Hello,

 

I've implemented the following code from the linked paper and this works fine in PC SAS 9.4, however I cannot get it to work on the server version of Enterprise Guide 7.1. 

 

When I try to run the code, I get the error below and I believe this is coming from the filename statement:

 

ERROR: Insufficient authorization to access /sasfs/prod/sas/94/controlserver/sasconfig/Lev1/SASApp94/xl2sas.dat.

 

Please can you help.

 

Many thanks,

 

Kriss


Accepted Solutions
Solution
‎11-07-2017 11:45 AM
Super User
Super User
Posts: 8,174

Re: SAS EG and DDE

If you can use libname excel:

https://blogs.sas.com/content/sasdummy/2015/05/20/using-libname-xlsx-to-read-and-write-excel-files/

 

That would be the ideal solution as it does it all for you, you set a libname, then write or read from that directly.

 

If you can't, then it would be a macro loop, with the sheet names being put in manually.  Or you could dump the Excel file out to various CSV files, using VBA its a very simple macro to do.  Then your SAS can read in the various CSV files.

View solution in original post


All Replies
Super User
Super User
Posts: 8,174

Re: SAS EG and DDE

The simple answer: Don't use DDE.  That technology is well over 15 years out of support now.  It was designed as a connection to Office files, but years since then even MS dropped support for it.  It won't work across networks and such as it needs to be able to open Office - i.e. on the server which may not be able to run office.

 

Consider what you are doing as there are far better methods to get data out to Excel (or drop Office totally for the betterment of everyone).  For instance: 

proc export (if available)

pcfiles server

ods tagsets.excelxp

ods excel

libname excel (again if available)

Write to text file CSV or XML

Write to PDF for reports

Super Contributor
Posts: 256

Re: SAS EG and DDE

Thank you @RW9, I guess I'll just use Proc import then. Do you know if there is a quick way to import all the sheets which are in Excel into SAS?

 

Thanks,

 

Kriss

Solution
‎11-07-2017 11:45 AM
Super User
Super User
Posts: 8,174

Re: SAS EG and DDE

If you can use libname excel:

https://blogs.sas.com/content/sasdummy/2015/05/20/using-libname-xlsx-to-read-and-write-excel-files/

 

That would be the ideal solution as it does it all for you, you set a libname, then write or read from that directly.

 

If you can't, then it would be a macro loop, with the sheet names being put in manually.  Or you could dump the Excel file out to various CSV files, using VBA its a very simple macro to do.  Then your SAS can read in the various CSV files.

Super Contributor
Posts: 256

Re: SAS EG and DDE

Thank you RW9! :-)

☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 117 views
  • 1 like
  • 2 in conversation