BookmarkSubscribeRSS Feed
deleted_user
Not applicable
I am working on automating a set of reports that would need to be formatted including the page setup and print settings. Is there a way to export a data set to an existing spreadsheet starting the data on the second row?

I want to export the data to the existing sheet, then rename the file and email it out through a macro since there are hundreds of recipients of the report.

Thanks in advance for any help.
4 REPLIES 4
deleted_user
Not applicable
ods tagsets.excelXP file= yourfile rs= none ;
........... generate reports
ods tagsets.excelXP close ;

see the details at
http://support.sas.com/rnd/base/ods/odsmarkup/index.html.

This is a developing area, so make sure you get the latest download

PeterC
Cynthia_sas
SAS Super FREQ
Hi:
And just a clarification of what TAGSETS.EXCELXP is doing. It is NOT doing an "official" EXPORT to an Excel workbook/worksheet. So you could NOT, for example, add information to an existing workbook using TAGSETS.EXCELXP.

However, TAGSETS.EXCELXP will create a NEW Workbook with Multiple Worksheets (if you want multiple sheets) and TAGSETS.EXCELXP will write the correct XML tags in the output file so that Excel maintains the ODS style formatting. TAGSETS.EXCELXP also has options that allow you to specify page setup (like gridlines) and print settings (like portrait, landscape or print headers) for Excel when Excel opens the file.

You can even email the file that's created directly from SAS, if your folks have or will turn on your email server capability.(They have to define the email info in the SAS startup routines.)

But every time you run TAGSETS.EXCELXP, you would either be creating a new file or writing over an existing file. The TAGSETS.EXCELXP destination produces Microsoft-compliant Spreadsheet Markup Language XML tags around your procedure output to define how the workbook/worksheets should be rendered when Excel opens the file that ODS creates. It is not technically an EXPORT because it works in a different way than the way PROC EXPORT creates an Excel binary file. (In fact, I think TAGSETS.EXCELXP is a better method than PROC EXPORT if you want to be able to set colors, fonts and Excel settings -- because TAGSETS.EXCELXP has those capabilities and PROC EXPORT does not.)

cynthia
deleted_user
Not applicable
Thanks for the advice. It is much appreciated.
deleted_user
Not applicable
You can also use DDE, to populate existing excel template. There was a poster paper on SAS Global Forum 2008 that shows this step by step.

here is the link

http://www2.sas.com/proceedings/forum2008/229-2008.pdf

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!

Discussion stats
  • 4 replies
  • 858 views
  • 0 likes
  • 2 in conversation