BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
djrisks
Barite | Level 11

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

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

djrisks
Barite | Level 11

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

djrisks
Barite | Level 11

Thank you RW9! 🙂

Maggie10
Calcite | Level 5

hi SAS

I have the same problem. The reason that we go through the long route from SAS -> Excel -> PDF, because we can have better graphs in excel after data dumped into excel template, and we need to have pdf files by converting hundreds of excel work sheets into pdf and assembled them together. 

We need to know how to trigger VBA macros to do these two things: Save excel to pdf and attached them together. This can be done by DDE before in local setting. But we cannot trigger VBA in SAS studio. How can we save hundreds of excel worksheet into one PDF file?

 

Thanks

Maggie

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Please start a new thread with any new questions rather than re-opening old ones.

In terms of graphs, SAS sgplot/gtl is vastly superior to Excel, and boasts the analysis backend of SAS as well.  There really is no benefit to using Excel, it is really a fact of life that its so prevalent that its used not that it is in any way better than other solutions.  Now if you compared SAS graphing to R graphing then that is a different matter, and with Shiny and such like R is far better.  But compared to a childs toy like Excel...

DDE is ancient, it went out of support back before xls was removed from support so >15+.  It really shouldn't be being used in any context, as you found here, and plenty of other areas, it will not work at all.

VBA is pretty cool for scripting and such like within the Office application, it is however tied to Office and Windows - thus you are limiting your options to proprietary software only available in certain places, not ideal.  

Far better to learn how to create graphs using sgplot/gtl (or R), output these directly to PDF's, or to RTF, then combine them through that method.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 3405 views
  • 1 like
  • 3 in conversation