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
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.
**************************************************************
