Exporting Data from SAS 8.02 to Excel 2000

[msprichj]

My archaic company has yet to upgrade to SAS 9 (and the benefits therein for exporting to Excel). As such, we have problems, which I'm hoping you folks can help with!

Firstly, the way we do things at the moment mean that we issue commands in SAS to open Excel and then use PUT commands to write data to predefined Excel templates. These jobs run overnight on a local PC, but will only work if the PC is unlocked; if it's locked when attempting to run this scheduled task, we get an error message in Excel saying that there are "Not enought system resources to display the data", even if the data we're attempting to write to Excel is very small - perhaps only a couple of rows or columns. Any idea why this happens when the machine is locked? (Our IT dept is clueless!)

That said, the absence of useful tagsets in SAS 8 (such as the ExcelXP) makes it difficult to export good looking, multiple-tabbed sheets to an Excel workbook, without having to open up Excel (and thereby creating the problems above!). I've managed to use a nice template and the ODS (HTML option)to create good output to a single-sheet Excel workbook (effectively a HTML file, but with an XLS extension). This avoids having to open Excel and the problem mentioned above.

So, any ideas what's causing the first problem and how to fix it, or/and how to get good looking, multiple-tabbed Excel files from SAS 8 into Excel 2000, without having to open Excel and use PUT commands?

Desperate for help!
Cheers
Rich

 

[ChrisW75]

Yeesh. SAS vs Excel has given me many headaches over the years. Fortunately I've never needed to do this in version 8. Unfortunately for you this means I can really offer any suggestions.
For the first problem I would suggest asking SAS support directly, they might have a fix available. Is the error message generated by Excel or SAS?

I'd really recommend upgrading to version 9, it's pretty painless, I worked in the IT department at my last job and helped run the upgrade to version 9 and we encountered only minimal problems. You can easily run both versions side by side as well, which we did for about 4 months before the final changeover to give the users time to test things out. When the final changeover happened, it was pretty seamless.

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

 

[msprichj]

Thanks for your efforts and recommendations!
After considerable hunting around, I found a suggestion on another site that suggested that when the machine is locked, the desktop is no longer technically owned by the logged-in user, so it won't ouput to open windows. Instead, one has to make the Excel application invisible with some VBA code and then it writes fine using DDE. It works, even though it seems a slight fudge.
I've been encouraging the company to upgrage for a while, but I think they're a bit tight.
Still, thanks again for your help!
Rich

 

[annalarpent]

Hey msprichj, this is anna larpent - would you mind adding me on facebook? Thanks!