Reeza wrote: Here's a link to a presentation I did on the topic which includes some sample code.  The presentation isn't really standalone, but should get you started. Make sure to review the drawbacks slide - this won't work in all cases. http://www.sas.com/content/dam/SAS/en_ca/User%20Group%20Presentations/Edmonton-User-Group/FareezaKhurshed-CoordinateComplexReports-Spring2014.pdf 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. I will look at your presentation, though. I'm sure I'll learn something new. Thanks for the reply. ... View more

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

Reeza wrote: Another simple method is to create a extra tab in your worksheet and include the data there in a named range. Link the formatted areas of the excel sheet to the named range data. Then export to the named range using a standard export procedure and the data will be updated. Reeza, What do you mean by "standard export procedure"? Is that a proc export? If so, how do I specify the named range? Thanks. ... View more

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? ... View more

I retyped the macro part and it worked. Thanks again. ... View more

I discovered that if I try to copy and paste from SAS EG 5.1 that I'm not able to paste into here. But if I paste into Notepad and then copy and paste from Notepad into here, it works. Weird. Thanks for all of the help. ... View more

Thanks for the help. I did use this and it worked. ... View more

Thanks for the reply. It did work. ... View more

I tried it but you were right, it did have a few dupes. I need to have only one row per id. Reeza's code in an above message worked for what I need. Thanks for replying. ... View more

Those worked great. Thanks Reeza. ... View more

I'll try this. Thanks. > try following options, this will enable the query to > be sent to database > > libname ods oracle user =user,password="pwd" > ,path=path,schema=schema; > options DBIDIRECTEXEC; > %let SYS_SQL_IP_SPEEDO=YES; > proc sql; > select * from ods.table1; > quit; ... View more

I don't know her reasons. I'm going to test using the passthru, though, considering that it took almost 4 hours to run the query in SAS without passthru and less than a minute in TOAD. > I would be interested in knowing why your boss does > not recommend using PASSTHRU SQL. The only negative I > can think of is the syntax differences. If you are > familiar with Oracle SQL that problem disappears. > > FYI, we use PASSTHRU SQL almost exclusively for > complex queries as you can easily add Oracle > specifics like HINTS that can dramatically improve > performance (for example query parallelisation). And > you can test your query in TOAD and then insert it > unchanged into your SAS program and expect very > similar performance. These are important issues for > large, complex production processes that need to run > as efficiently as possible. It also removes the issue > of having to investigate how SAS is translating your > SQL. > > I would agree however that SAS SQL and language is > the way to go for simpler processes and smaller data > volumes where ultimate processing efficiency is not > required. ... View more