HOW TO EXPORT MULTIPLE REPORTS IN TO MULTIPLE SHEETS IN ONE EXCEL WORKBOOK HTML THROUGH .XLS

Reply
Occasional Contributor
Posts: 15

HOW TO EXPORT MULTIPLE REPORTS IN TO MULTIPLE SHEETS IN ONE EXCEL WORKBOOK HTML THROUGH .XLS

HOW TO EXPORT MULTIPLE REPORTS IN TO MULTIPLE SHEETS IN ONE EXCEL WORKBOOK

HTML THROUGH .XLS

Esteemed Advisor
Esteemed Advisor
Posts: 7,248

Re: HOW TO EXPORT MULTIPLE REPORTS IN TO MULTIPLE SHEETS IN ONE EXCEL WORKBOOK HTML THROUGH .XLS

You are talking about several different things there.  The easiest way to get data out to multiple sheets in Excel is to use the ExcelXP tagset:

ods tagsets.excelxp file="abc.xlsx" options=(sheet_name="ABC");

proc report...;

run;

ods tagsets.excelxp options=(sheet_name="DEF");

proc report...;

run;

ods tagsets.excelxp close;

Occasional Contributor
Posts: 15

Re: HOW TO EXPORT MULTIPLE REPORTS IN TO MULTIPLE SHEETS IN ONE EXCEL WORKBOOK HTML THROUGH .XLS

HTML THROUGH .XLS CARRIES SAS DATASET FORMATS LIKE COMMA AND PERCENT THAT IS THE REASON TRAING THIS.                                     ODS TAGSETS.EXCELXP FILE IS CREATING ,BUT FILE IS CAN'T OPEN .ERROR IS UNABLE TO READ         PLEASE TELL ANY OTHER WAY.                           THANKS

Esteemed Advisor
Esteemed Advisor
Posts: 7,248

Re: HOW TO EXPORT MULTIPLE REPORTS IN TO MULTIPLE SHEETS IN ONE EXCEL WORKBOOK HTML THROUGH .XLS

Sorry, I can't really make head nor tail of your post.  Tagsets.excexp can create formats on the output:

As for the file not opening, have you printed any data, is the tagset opened, and closed, etc.

Occasional Contributor
Posts: 15

Re: HOW TO EXPORT MULTIPLE REPORTS IN TO MULTIPLE SHEETS IN ONE EXCEL WORKBOOK HTML THROUGH .XLS

Actually what the problem is that in a variable its having two different formats. i.e. comma and percent.so i used proc format below.

proc format;

value fmt

low-0, 1-high=[comma32]

other=[percent12.2];

run;

i applied this macro for all the variables. again by using ods tagsets.excelxp in output file only comma format is applying percent is missing.

To solve the problem i used ods html with .xls extension. now the report contain comma and percent format in one cell.

Now using ods html how we can send multiple reports into multiple sheets in a single file.

is it possible using only ods html with .xls extension.

Esteemed Advisor
Esteemed Advisor
Posts: 7,248

Re: HOW TO EXPORT MULTIPLE REPORTS IN TO MULTIPLE SHEETS IN ONE EXCEL WORKBOOK HTML THROUGH .XLS

With proc report you can call define formats and other style items, here are some examples:

Base SAS(R) 9.2 Procedures Guide

https://support.sas.com/resources/papers/proceedings11/266-2011.pdf

Thus it does not matter what "destination" is used, HTML, tagsets etc. but what you code into the proc report and especially the call define blocks.

I would however point out that having multiple formats on one column is not a good idea.  Any properly structured database, or data handling software will have fixed columns formats.  The fact that Excel lets any old rubbish in any old cell is just another great reason not to use it! 

Occasional Contributor
Posts: 15

Re: HOW TO EXPORT MULTIPLE REPORTS IN TO MULTIPLE SHEETS IN ONE EXCEL WORKBOOK HTML THROUGH .XLS

actually iam tring this

code

ods listing close;

ods tagsets.html3  file="D:\EMPTY\test88.xls"

options(sheet_interval='table' suppress_bylines='yes' sheet_name='#byval1')

  options(sheet_interval='table' suppress_bylines='yes' sheet_name='#byval2')

  options(sheet_interval='table' suppress_bylines='yes' sheet_name='#byval3');

proc print data=sashelp.cars;

run;

proc print data=sashelp.class;

run;

proc print data=sashelp.cars;

run;

ods _all_ close;

ods listing;

these 3 reports came in one excel sheet ,but requirement is 3reports are come in 3 sheets.

can any one helpme .

thanks in advance.

Ask a Question
Discussion stats
  • 6 replies
  • 415 views
  • 0 likes
  • 2 in conversation