LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (February 2007, week 2)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:         Mon, 12 Feb 2007 23:57:55 -0700
Reply-To:     Alan Churchill <SASL001@SAVIAN.NET>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Alan Churchill <SASL001@SAVIAN.NET>
Subject:      Re: DDE file open is very slow
Comments: To: "Johnson, David" <David.Johnson@CBA.COM.AU>
In-Reply-To:  <FD746D3C07FB0C478AB951C18CE266A4A928E2@aaunsw412.au.cbainet.com>
Content-Type: text/plain; charset="iso-8859-1"

David,

Let me address these in order.

In my 'pull' recommendation, I can completely control what is put into an Excel cell...from format to type to color to borders. I can control everything cell-by-cell and not just top-to-bottom. When I am ready to write a formula, I write it into that cell and it could be the first action taken...before the data is even in the sheet.

Protection level. No problem. Do it as you see fit.

Level of customization: endless. Full access to everything Excel offers.

Microsoft prides themselves on backwards compatibility so VBA is deprecated but it will always run. Doesn't mean itís the best though.

I am running around 50 spreadsheets a second from SAS datasets with every sheet fully formatted, printing options set, AND in native Excel format. No option out there whether it is DDE, ODS, Libname, etc. can hold a candle to the process I show in the paper. Whether it is speed or flexibility, I stand by this methodology.

Bottom-line is that people view the problem of SAS to Excel as SAS MUST create Excel spreadsheets or a format that Excel understands (XML, HTML). However, stand the problem on its head and have Excel (or an Excel engine) read SAS. SAS datasets are easily read using OleDb so why not reverse it and try a pull technique?

Alan

Alan Churchill Savian "Bridging SAS and Microsoft Technologies" www.savian.net

-----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Johnson, David Sent: Monday, February 12, 2007 11:36 PM To: SAS-L@LISTSERV.UGA.EDU Subject: Re: DDE file open is very slow

Thanks Alan,

There are however some key problems for which DDE has been the only solution.

How to put an account "number" into an Excel cell and not have it translated into a number and then formatted using scientific notation,

How to populate a spreadsheet in a customised layout such as: data dependent titling, one summary row, five detail rows, one calculated row etc...

How to set protection on a spreadsheet at the cell level so you can provide users with a verification spreadsheet that can only be changed in certain areas without rows or columns lost or inserted / cells overwritten in error / data reordered / etc. Excel may not be ideal for that sort of data entry and correction, but it is faster than my building an AF application and runs on all desktops.

How to format reporting data for easy readability in Excel.

DDE is not my first choice. I prefer to use the SAS native export / import / DBLoad / Access procedures where possible because the code is much trimmer. But sometimes you need a tool with micrometer adjustments <grin>

VBA may be deprecated but Microsoft are still making the Macro V8 help file available. At least when SAS deprecate a procedure (like Delete) it disappears without trace from the documentation. I find that message to be very clear.

Kind regards

David

-----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Alan Churchill Sent: Tuesday, 13 February 2007 4:14 PM To: SAS-L@LISTSERV.UGA.EDU Subject: Re: DDE file open is very slow

David,

Whenever you work with DDE, you are at the mercy of COM and all of its problems and weirdness. VBA is deprecated as I have stated several times, and shouldn't be used.

I am finalizing my paper on How to Generate 10,000 Excel Spreadsheets in 10 minutes (or less) for SGF. It provides a better solution to SAS to Excel interaction that I hope will help people out of binds similar to what you discovered. It relies on pull technology versus put and is super-fast and should be error-free. It also doesn't require SAS or Excel to be installed on the machine to operate.

Alan

Alan Churchill Savian "Bridging SAS and Microsoft Technologies" www.savian.net

-----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Johnson, David Sent: Monday, February 12, 2007 9:21 PM To: SAS-L@LISTSERV.UGA.EDU Subject: Re: DDE file open is very slow

This may serve as a cautionary tale for others, so I'll provide an update.

When you ask a nearly right question of Google you get answers that lead you to the right questions. How often do we find that asking the right questions is key to our work???

Where OpenGL isn't native on graphics cards, there is an emulator built in. This makes it available on most Windows machines. As an open source graphics language, it is probably also the choice of many for the creation of graphic objects including screen savers. However, cautionary tales on the web indicate it is a resource hog.

While SAS seems to be able to wrest back CPU from OpenGL, not all applications can, and VBA seems to be especially vulnerable to being starved of processing capacity. So VBA apps may, and often do stall.

DDE has the same vulnerability because of shared parentage. So, it is likely some DDE commands will not execute on machines where an OpenGL screensaver is active.

Cause probably found, solution still outstanding...

Kind regards

David

-----Original Message----- From: Johnson, David Sent: Tuesday, 13 February 2007 11:57 AM To: SAS-L@LISTSERV.UGA.EDU Subject: DDE file open is very slow

After running batch jobs for more than six months which opened, created, updated and saved Excel files with DDE, I have never seen this issue before.

A batch run last night, the first time on this Windows desktop machine, started Excel, saved some data and closed the Excel file.

Unfortunately, the Excel start-up took 5hrs 38 minutes, which was the time between the SAS code file starting and my unlocking the screen saver.

On opening the desktop, there were no dialog boxes seen except for the SAS batch windows and process crawlers.

Excel is started minimised, and I didn't see anything untoward from that application.

There is only one Excel instance, so it isn't a personal.xls conflict.

The default book on opening is book1, and this doesn't exist.

The data disk is local, so it isn't prima facie a transient network issue at 5am today.

The startup command and data step has been run repeatedly over the last few weeks in an interactive session, and usually Excel startup is virtually immediate.

The screensaver was operational, but I was still logged in.

Has anyone else had this problem and perhaps noticed any other symptoms?

I shall run some Mickey Mouse batches today and try to replicate the problem, but I don't hold out any great hope. Any other experiences should help narrow the suspects.

Kind regards

David

************** IMPORTANT MESSAGE ***************************** This e-mail message is intended only for the addressee(s) and contains information which may be confidential. If you are not the intended recipient please advise the sender by return email, do not use or disclose the contents, and delete the message and any attachments from your system. Unless specifically indicated, this email does not constitute formal advice or commitment by the sender or the Commonwealth Bank of Australia (ABN 48 123 123 124) or its subsidiaries. We can be contacted through our web site: commbank.com.au. If you no longer wish to receive commercial electronic messages from us, please reply to this e-mail by typing Unsubscribe in the subject line. **************************************************************

************** IMPORTANT MESSAGE ***************************** This e-mail message is intended only for the addressee(s) and contains information which may be confidential. If you are not the intended recipient please advise the sender by return email, do not use or disclose the contents, and delete the message and any attachments from your system. Unless specifically indicated, this email does not constitute formal advice or commitment by the sender or the Commonwealth Bank of Australia (ABN 48 123 123 124) or its subsidiaries. We can be contacted through our web site: commbank.com.au. If you no longer wish to receive commercial electronic messages from us, please reply to this e-mail by typing Unsubscribe in the subject line. **************************************************************


Back to: Top of message | Previous page | Main SAS-L page