BookmarkSubscribeRSS Feed
wkossack_nspirehealth_com
Calcite | Level 5

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.

18 REPLIES 18
OS2Rules
Obsidian | Level 7

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
Super User

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
Obsidian | Level 7

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
Calcite | Level 5

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

wkossack_nspirehealth_com
Calcite | Level 5

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
Super User

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
Barite | Level 11

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

---->-- ja karman --<-----
wkossack_nspirehealth_com
Calcite | Level 5

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

jakarman
Barite | Level 11

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

---->-- ja karman --<-----
Reeza
Super User

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
Calcite | Level 5

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
Calcite | Level 5

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
Barite | Level 11

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?

---->-- ja karman --<-----
wkossack_nspirehealth_com
Calcite | Level 5

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 18 replies
  • 2632 views
  • 0 likes
  • 5 in conversation