SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

Exporting SAS data sets to existing spreadsheet?

Reply
N/A
Posts: 0

Exporting SAS data sets to existing spreadsheet?

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.
N/A
Posts: 0

Re: Exporting SAS data sets to existing spreadsheet?

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
SAS Super FREQ
Posts: 8,813

Re: Exporting SAS data sets to existing spreadsheet?

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
N/A
Posts: 0

Re: Exporting SAS data sets to existing spreadsheet?

Thanks for the advice. It is much appreciated.
N/A
Posts: 0

Re: Exporting SAS data sets to existing spreadsheet?

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
Ask a Question
Discussion stats
  • 4 replies
  • 301 views
  • 0 likes
  • 2 in conversation