Loading data into an excisting excel using ODS

[RW9]

Note, this post is from 2013!  IMO your on a road to nowhere there, trying to fit a structured controlled set of data into an uncontrolled free for all excel mess.  You will hit many problems.  There are many options to get data out of SAS:

DDE - old technology, wouldn't recommend.

Export - basic guessing

tagsets.excelxp - creates nice functional reports

CSV - basic data output

Now we look at what you want, to include a third party software.  This doesn't fit to any of the above, so your going to have to force it somehow.  Me, I would export your data as CSV.  Then in your Excel template files have a OnLoad VBA macro which locates this CSV file, loads it and processes it into your template file. 

The other way of course, is to libname to your Excel file, and write data directly: http://www2.sas.com/proceedings/sugi31/024-31.pdf

Personally I don't like it as it adds a locking to the file plus potential other things.

Either way your trying to force your data into the hole which is Excel, be prepared for things changing in the Excel file, mismatches, lack of validation etc.

[DanD]

RW9 wrote:

Note, this post is from 2013!  IMO your on a road to nowhere there, trying to fit a structured controlled set of data into an uncontrolled free for all excel mess.  You will hit many problems.  There are many options to get data out of SAS:
DDE - old technology, wouldn't recommend.
Export - basic guessing
tagsets.excelxp - creates nice functional reports
CSV - basic data output
Now we look at what you want, to include a third party software.  This doesn't fit to any of the above, so your going to have to force it somehow.  Me, I would export your data as CSV.  Then in your Excel template files have a OnLoad VBA macro which locates this CSV file, loads it and processes it into your template file. 
The other way of course, is to libname to your Excel file, and write data directly: http://www2.sas.com/proceedings/sugi31/024-31.pdf
Personally I don't like it as it adds a locking to the file plus potential other things.
Either way your trying to force your data into the hole which is Excel, be prepared for things changing in the Excel file, mismatches, lack of validation etc.

Thanks for the reply. We don't have the libname engine.

However, I was able to do what I wanted using proc export like this:

/*    export the data for the provider to a spreadsheet */

proc export data=&provider

file="&prgdata./&provider._test.xlsx"

dbms=xlsx replace;

sheet='Template';

      run;

  I think this is new in 9.4.

[data_null__]

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.

[wkossack_nspirehealth_com]

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.

[data_null__]

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.

[wkossack_nspirehealth_com]

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

[data_null__]

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.

[wkossack_nspirehealth_com]

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

[wkossack_nspirehealth_com]

nope none of the solutions seem to work when run in batck.  The code runs several times a day to update reports

any other ideas?

[data_null__]

You need to show your work and give more details.  I don't even know what you called solutions and I haven't seen any code.

In this thread is my program to save xml as xlsx.  I don't know if it will help you but it finishes the conversion before SAS continues plus it is good example of stored compiled data step.

https://communities.sas.com/message/124238#124238

Message was edited by: data _null_