does 9.4 work with excel?

[wkossack_nspirehealth_com]

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.

[OS2Rules]

Hi:

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.

[Reeza]

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.

[OS2Rules]

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.

[wkossack_nspirehealth_com]

we have office 2013 pro installed on the server with the correct license for multiple users

[wkossack_nspirehealth_com]

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.

[Reeza]

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.

[jakarman]

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

[wkossack_nspirehealth_com]

SAS logs are saved to a folder but they don't provide any messages about what the vbscript or excel macro are doing

[jakarman]

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

[Reeza]

wkossack@nspirehealth.com wrote:

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.

[wkossack_nspirehealth_com]

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

[wkossack_nspirehealth_com]

so from all the replies it looks like nobody is creating xls from 9.4 and possibly what I have been told is not true

[jakarman]

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?

[wkossack_nspirehealth_com]

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.