Loading data into an excisting excel using ODS

[pawan]

Need some help in writing data into an Excel using ODS HTML( or anyother ODS like MSOFFICE, etc), where I would like to add current date to the excisting Excel file name. Any help would be highly appreciated. Thanks in advance.

[Reeza]

You can't.

ODS doesn't create an Excel file, it creates a file that Excel can read, such as HTML, XML.

You can't write into a native Excel file.

You can export data to an excel file using proc export or using DDE.

[pawan]

Thanks Reeza, I'm needed to accomodate some 'Titles' into Excel reports. So, just thought if I can do it using ODS!! Would it be possible using PROC APPEND or any other procedure, if not through EXPORT??!!

[Reeza]

More details please.

[pawan]

As I do reporting stuff, I generally use pre-formatted/pre-aligned Excel files as templates and load data into these files periodically. Whenever I load the data I get created with a latest report with defined formats, fonts, alignment etc., I do all these using DATASETS and APPEND procedures. Everything was fine, until I got a new request to insert some Header titles into these reports. Any ideas pls help.. thanks in advance.

[Reeza]

So export a dataset with titles/headers and then link them in the appropriate location in your formatted/pre-aligned excel file. Its just more data that goes in a different place.

[wkossack_nspirehealth_com]

this is a problem similar to a battle I have been fighting

I have a ton of code that outputs to excel using ODS for a dozen projects.  However, the files come out as xml (or html) yet I have the requirement that the 'reports' are in excel.

To fix this I had to write a block of code that creates a vbscript file.

First my code outputs the all the xml reports.  Then I create the vbscript that opens each xml spreadsheet and and then does a save as xlsx.  At the end it deletes all the xml files.

One problem I've encountered is the timing of batch runs for a dozen or so projects.  I have to put waits or sleep commands into my code so a process will have a chance of finishing before another process runs.

I wish SAS could output to a native excel file.  I'm debating writing an VB program that handles this for all projects

[pawan]

Instead, you can define LIBNAME statements with EXCEL engine and can use procedures like APPEND within a macro and then invoke the same macro for all the needed projects. I can say this would be a very simple code but bit of workout before you get everything right.

[Reeza]

You don't necessarily need Append, you can write datasets directly to native excel files.

We just automated 132 reports of 50 pages each that have various graphs/tables/text from a predefined excel template that is populated via SAS.  It's easier than you'd think

[wkossack_nspirehealth_com]

using excel templates might be an option except all the reports would need to be revalidated.  Everything is written using ODS output

[Reeza]

If its working why break it

[wkossack_nspirehealth_com]

I'm always looking for a better way

If you don't do that we would still be using stone tools and programming in hex on cave walls....and when the cave crashes things can be really bad

[Reeza]

My philosophy is to not fix it if it's working, but new things get done a different/better way as I go along. 

[wkossack_nspirehealth_com]

ah but this is a situation where it is not working 100%.

For example, some clients have problems accessing xml reports.

There is a problem with Internet explorer.  A web portal is used to view the reports.  Myself and some clients are unable to view xml files as excel using Internet Explorer (this is a known bug that came into IE with an update a couple months ago on my computer).  If you try to open the file it is opened in an xml editor and there appears to be no way of telling IE that it should use excel to open XML files.

[DanD]

Reeza wrote:

You don't necessarily need Append, you can write datasets directly to native excel files.

We just automated 132 reports of 50 pages each that have various graphs/tables/text from a predefined excel template that is populated via SAS.  It's easier than you'd think 

Reeza,

I have a very similar situation. I need to output 1200 reports (one for each provider) using a template that contains graphs, charts, etc. The first tab contains the rows of data and the other 10 tabs pull from the first tab to create the graphs and charts. Can you tell me how you did it?