08-14-2013 09:40 AM
I have a big problem with 9.3 64 bit on windows 2008 R2.
I have code that creates reports in excel.
One program uses DDE to kick off an excel macro that draws charts and does a save as excel. 9.3 will only save excel as xml and all reports need to be in excel format. This process works perfectly if I run interactively or by right clicking on the file and selecting run in batch 9.3. However, during nightly batch which runs under a different user ID 'SAS' it does not complete and leaves both SAS and excel processes hung.
Another process is simply a vbscript that iterates through all the files in a folder and does a save as xlsx then deletes the xml file. This does not run at all.
What is really tough is there are no messages coming from the system or the runs to tell me why things don't work in batch.
I've heard that 9.4 can create actual excel spreadsheets. Is this true? Has anyone tried it? Or is this just hot air.
08-14-2013 10:15 AM
1) We run Excel in both batch and interactive using ExcelXP tagset and have no problems. We also have a large number of overnight batch jobs (hundreds) that run from Autosys and create Excel output without problems.
2) When users request it, we create native Excel files by running the VB script that saves the xml files created from the ExcelXP ODS process as native xls files. This is run as a macro from the same job that creates the
original report. Again - these processes all run without problems
Note - this all runs in both 9.1 and 9.3 on a Windows 2003 system.
I think your main problem is that you aren't capturing the logs from the batch processes - I think that there must
be some error that is not being identified rather than SAS not being able to do what you want.
08-14-2013 10:29 AM
I think any version of SAS can create an native excel file using proc export, assuming the correct license. However, if you're on a server you may not have Excel on the Server and then certain things may not be possible.
08-14-2013 10:34 AM
You can run the ExcelXP tagset and create the xml files on any platform - I've run many reports on a z/OS mainframe and there is no Excel license there. The problem would only happen when running the VB script to convert to native Excel formats.
08-14-2013 11:23 AM
the reports need to be formatted with titles and footnotes for users to read so I don't think export would do the trick
clients access the reports through a web portal. There is an unsolved problem with windows that will not open xml in excel through a web browser. I've had this problem myself for most of this year so I have to go to the actual folder where the reports are kept to view them.
08-14-2013 11:25 AM
Ok, but DDE will do the titles and formats. You mentioned DDE in your question by the way, not tagsets.
I think you need to clarify your question, with more details about your process.
08-14-2013 10:43 AM
The missing of any logfile saying something is dissappointing.
You are saying using a different user and DDE excel. Some addtional checkpoints:
- Is SAS and Excel both working with the other user?
- The DDE interface is actually connecting to an interactive Excel session
It this possible wiht that type of batch-processing? is there some check Excel has been started?
Is it communicating with the correct Excel session ? (only the first I can imagine).
08-14-2013 10:55 AM
You could have hit something strange witin the OS like: 41863 - DATA step does not run completely when you use the PIPE engine in Vista, Windows 7 and Windo...
08-14-2013 11:20 AM
One program uses DDE to kick off an excel macro that draws charts and does a save as excel. 9.3 will only save excel as xml and all reports need to be in excel format.
How are you saving the file through DDE? When you use save() there's a bunch of different formats, in fact I save some of my excel files out to PDF files.
08-14-2013 11:36 AM
sas creates the xml file using the ods excelxp tagset. This contains a number of tables
the using DDE SAS opens another spreadsheet that holds an excel macro that goes through all the tables and draws charts in the xml spreadsheet. At the end it does a save as xlsx then closes everything
08-15-2013 12:53 AM
wkossack your header is mentioning 9.3 but in the descritption it is 9.3. Wich one is true?
wkossack your telling the SAS programming and DDE works with one user, true?
The problem is this doesn't work when you run that with an other user as batch processes in your technical setup, true?
This kind of issues are quite common to be an issue. I personal have seen them many many year.
We can give you only tips you can do to do research for that issue as it is related to your possible unique technical environment. True?
08-15-2013 09:23 AM
I have struggled with this problem for a month. SAS tech support has not been able to help and our IT can't figure it out.
The strange thing is that when using the SAS ID that is used to run midnight batch the code works fine both interactively and if kicked off by right clicking and then selecting run in batch.
My only hope for solving this in a reasonable time is to upgrade SAS to 9.4 but that will only happen if 9.4 actually does provide better function by creating xls files and not xml. This unfortunately will cause a re-validation of a significant amount of code but I don't have any choice.