topic Re: Loading data into an excisting excel using ODS in ODS and Base Reporting

Loading data into an excisting excel using ODS

pawan — Wed, 24 Apr 2013 14:03:24 GMT

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.

Re: Loading data into an excisting excel using ODS

Reeza — Wed, 24 Apr 2013 14:21:34 GMT

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.

Re: Loading data into an excisting excel using ODS

pawan — Wed, 24 Apr 2013 14:28:21 GMT

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??!!

Re: Loading data into an excisting excel using ODS

Reeza — Wed, 24 Apr 2013 14:33:20 GMT

More details please.

Re: Loading data into an excisting excel using ODS

pawan — Wed, 24 Apr 2013 15:28:34 GMT

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.

Re: Loading data into an excisting excel using ODS

Reeza — Wed, 24 Apr 2013 15:30:35 GMT

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.

Re: Loading data into an excisting excel using ODS

wkossack_nspirehealth_com — Wed, 24 Apr 2013 15:35:49 GMT

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

Re: Loading data into an excisting excel using ODS

pawan — Wed, 24 Apr 2013 15:44:24 GMT

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.

Re: Loading data into an excisting excel using ODS

Reeza — Wed, 24 Apr 2013 16:02:28 GMT

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

Re: Loading data into an excisting excel using ODS

wkossack_nspirehealth_com — Wed, 24 Apr 2013 16:37:11 GMT

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

Re: Loading data into an excisting excel using ODS

Reeza — Wed, 24 Apr 2013 16:52:45 GMT

If its working why break it

Re: Loading data into an excisting excel using ODS

wkossack_nspirehealth_com — Wed, 24 Apr 2013 16:55:07 GMT

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

Re: Loading data into an excisting excel using ODS

Reeza — Wed, 24 Apr 2013 16:58:40 GMT

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

Re: Loading data into an excisting excel using ODS

wkossack_nspirehealth_com — Wed, 24 Apr 2013 17:11:01 GMT

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.

Re: Loading data into an excisting excel using ODS

data_null__ — Wed, 24 Apr 2013 17:11:45 GMT

Why not just added the "make it XLSX" into the programs that create the XML with tagsets. As soon as XML is created run "make it XLSX" using INFILE PIPE and the program will not continue until the VBSCRIPT is done.

Re: Loading data into an excisting excel using ODS

wkossack_nspirehealth_com — Wed, 24 Apr 2013 17:34:58 GMT

this is on server 2003

I'm currently doing

%include xmltoxls_save;

which builds the vbscript based on the xml files in the folder and at the end after creating the xlsx files it deletes the xml files. When I run the code interactively I can see how SAS ends execution and a minute later the xml files are deleted.

Re: Loading data into an excisting excel using ODS

data_null__ — Wed, 24 Apr 2013 17:53:16 GMT

I don't understand your point. You don't want SAS to end before the script is done? There are system options to have SAS wait for the external program it started to bend before continuing. The details depend on how you start the external program.

Re: Loading data into an excisting excel using ODS

wkossack_nspirehealth_com — Wed, 24 Apr 2013 18:37:39 GMT

OK I played around with how the code is run

data _null_;

x "cscript convert.vbs";

run;

seems to work best. My code was based on what others had writen and what tech support suggested.

the old code did a call system ("&vbscript"); where &vbscript was assigned in the macro that builds the script

Re: Loading data into an excisting excel using ODS

data_null__ — Wed, 24 Apr 2013 19:27:31 GMT

How are the X related system options set in your program? XSYNC and XWAIT. I'm not sure which one you will need. I "always" use PIPE or SYSTASK. You probably should be using SYSTASK with WAIT optionn.

Re: Loading data into an excisting excel using ODS

wkossack_nspirehealth_com — Wed, 24 Apr 2013 19:53:51 GMT

options noxsync noxwait;

tried the

command "cscript convert.vbs" wait;

both seem to run much faster and the window closes just before I see the files xml files get deleted. It used to run so the program would finish and a minute later I would see the files finish changing

seemed to run about the same. The acid test will be during midnight batch