BookmarkSubscribeRSS Feed
pawan
Obsidian | Level 7

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.

24 REPLIES 24
Reeza
Super User

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

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

More details please.

pawan
Obsidian | Level 7

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

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

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

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

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 Smiley Happy

wkossack_nspirehealth_com
Calcite | Level 5

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

Reeza
Super User

If its working why break it Smiley Happy

wkossack_nspirehealth_com
Calcite | Level 5

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

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

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

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?

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
  • 24 replies
  • 1801 views
  • 3 likes
  • 6 in conversation