topic Re: does 9.4 work with excel? in ODS and Base Reporting

does 9.4 work with excel?

wkossack_nspirehealth_com — Wed, 14 Aug 2013 13:40:15 GMT

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.

Re: does 9.4 work with excel?

OS2Rules — Wed, 14 Aug 2013 14:15:26 GMT

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.

Re: does 9.4 work with excel?

Reeza — Wed, 14 Aug 2013 14:29:33 GMT

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.

Re: does 9.4 work with excel?

OS2Rules — Wed, 14 Aug 2013 14:34:06 GMT

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.

Re: does 9.4 work with excel?

jakarman — Wed, 14 Aug 2013 14:43:53 GMT

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

Re: does 9.4 work with excel?

jakarman — Wed, 14 Aug 2013 14:55:30 GMT

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 Windows 2003 and 2008 server operating environments

Re: does 9.4 work with excel?

Reeza — Wed, 14 Aug 2013 15:20:57 GMT

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.

Re: does 9.4 work with excel?

wkossack_nspirehealth_com — Wed, 14 Aug 2013 15:23:19 GMT

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.

Re: does 9.4 work with excel?

Reeza — Wed, 14 Aug 2013 15:25:32 GMT

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.

Re: does 9.4 work with excel?

wkossack_nspirehealth_com — Wed, 14 Aug 2013 15:25:47 GMT

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

Re: does 9.4 work with excel?

wkossack_nspirehealth_com — Wed, 14 Aug 2013 15:27:12 GMT

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

Re: does 9.4 work with excel?

wkossack_nspirehealth_com — Wed, 14 Aug 2013 15:36:19 GMT

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

Re: does 9.4 work with excel?

wkossack_nspirehealth_com — Wed, 14 Aug 2013 15:40:16 GMT

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

Re: does 9.4 work with excel?

jakarman — Thu, 15 Aug 2013 04:53:30 GMT

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?

Re: does 9.4 work with excel?

wkossack_nspirehealth_com — Thu, 15 Aug 2013 13:23:14 GMT

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.

Re: does 9.4 work with excel?

Reeza — Thu, 15 Aug 2013 14:36:07 GMT

I still think you're very unclear with your question. It might be complex, but taking the time to elaborate is the only way you'll get solutions that work.

You mention DDE and XML. Are you using Tagsets? Are you using DDE? Why both?

Second, Are your Microsoft "Trust Settings" set up appropriately. I have a job here, and every time we set up a new user the template has to be set up as a trusted document for them.

Re: does 9.4 work with excel?

jakarman — Thu, 15 Aug 2013 15:54:17 GMT

Whosack you proved the SAS environment is running on both used id's.

Very nice because this eliminates that possible parts.

As all is working at SAS do not expect miracles being solved by upgrading. Although every support will tell you this.

"The next version will solve every problem, just wait" .... (humbug)

One of te last possibilities is something strange/weird in the batch scheduling you do and your manual actions as difference.

This beyond the scope of SAS and yet the should know. (google at microsoft for this type of questions)
Some examples:

- Run Powershell script as Scheduled task, that uses Excel COM object

- http://social.technet.microsoft.com/Forums/de-DE/d47d116e-10b9-44f0-9a30-7406c86c2fbe/scheduled-task-wont-run-bat-file

What could be the issue is the running key when started as scheduled job does not have the needed rights or resources.

- the scheduler is running by a key, probably "local server".

+ This key can not elevate rights.

+ some reources may have other settings (com is an example may be more)

- When running scheduled it is missing the keybord / screen resources. These do exist when used directly.

+ when some program is expecting those resources (call for them) then ... (wait/dump)

Why should SAS support know? Anser: It is common for Windows.

http://technet.microsoft.com/en-us/library/hh125927(v=ws.10).aspx

It is the same reason you have to change some rights (logn as batch job etc.)

Re: does 9.4 work with excel?

wkossack_nspirehealth_com — Wed, 02 Oct 2013 19:41:07 GMT

I was able to get things working. First, I discovered that a SAS program that calls an excel macro that draws charts using data output by SAS works in batch. That is it works in batch if I set up a scheduled task using my ID and password. If I set it up to run without providing a password it does not run. This same code does a save.as xls then erases the xlm file.

However, the vbscript program I wrote to do the conversion still did not work.

A couple weeks ago I had some free time and I wrote an excel macro to do the same thing as the vbscript. It works fine and converts the xml files to xls (I had a client that could not open xlsx files on his computer so I can not use that format.

I would still like to know what 9.4 adds to ODS. I heard a couple times from people that it is much improved

Re: does 9.4 work with excel?

Tim_SAS — Thu, 03 Oct 2013 12:57:58 GMT

wkossack@nspirehealth.com

I would still like to know what 9.4 adds to ODS. I heard a couple times from people that it is much improved

Check out What's New in the Output Delivery System for 9.4.