The SAS Output Delivery System and reporting techniques

Is it possible to put multiple tables from multiple data source in 1 sheet?

Reply
N/A
Posts: 0

Is it possible to put multiple tables from multiple data source in 1 sheet?

I try to put multiple tables from multiple data source into one Excel sheet by ODS and PROC REPORT procedures, but the difficulties are some have to be aligned in horizontal direction (blank gaps are needed between tables) and others are in vertical direction. Htmlpanel tagsets might be able to do the job, but how to insert gaps between tables is a real challenge to me. ODS OO and data _null_ don't seem to work with multiple data source in one output(please correct me if I am wrong). Thanks.
SAS Super FREQ
Posts: 8,868

Re: Is it possible to put multiple tables from multiple data source in 1 sheet?

Posted in reply to deleted_user
Hi!
First, you have to download the updated HTMLPANEL tagset and update your tagset template item store. (It's a good idea to download the latest version.

Then, try this program. It puts 2 tables side by side, separated by a blank column, with a proc tabulate underneath those 2 tables.

Remember that giving the output file a file type of ".xls" is just a convenience. You are really creating an HTML file.

Give this a try and see if it makes sense. Then to find out more about the HTMLPANEL tagset, you can visit http://support.sas.com/rnd/base/index.html and follow the links for ODS MARKUP and Tagsets.

Good luck,
cynthia

[pre]
ods path work.pan_ex(update)
sashelp.tmplmst(read);
/*
Download the latest HTML panel (HTMLPANL.TPL) tagset from support.sas.com
and %include it here to update the tagset in 9.1.3
*/

%let panelcolumns = 3;
ods noptitle;
ods listing close;

ods tagsets.htmlpanel file="testpanel.xls" options(panelcolumns='3');

/* start the panelling */
title 'High/Low Region Sales Comparison';
ods tagsets.htmlpanel event = panel(start);

proc report data=sashelp.shoes nowd;
column region n pctn;
define region / group order=freq;
define n /'Freq';
define pctn /'Percent' f=percent8.2;
run;

data blankcol;
x=' ';
run;

** this puts an "empty" or blank column between the 2 tables;
proc report data=blankcol noheaders nowd
style(report)={rules=none frame=void
cellspacing=0 borderwidth=0
bordercolor=_undef_}
style(header)={background=_undef_ foreground=_undef_};
column x;
define x /display;
run;

proc report data=sashelp.shoes nowd;
column region subsidiary sales;
define region/group;
define subsidiary/group;
define sales/sum;
break after region /summarize;
where region in ('Asia', 'Western Europe');
run;
ods tagsets.htmlpanel event=panel(finish);

%let panelcolumns = 1;
ods tagsets.htmlpanel event = panel(start);

proc tabulate data=sashelp.shoes f=comma14.2;
title 'Median and Total Sales';
class product region;
var sales;
table product=' ' all='Product Sales',
(region all='Region Totals')*sales=' '*(sum median)
/box='Sales Report';
where region in ('Asia', 'Western Europe');
run;
ods tagsets.htmlpanel close;

[/pre]
N/A
Posts: 0

Re: Is it possible to put multiple tables from multiple data source in 1 sheet?

Posted in reply to Cynthia_sas
Hi Cynthia,

Appreciate your quick response. It works quite well in my system after updating the tagsets. The dummy dataset serving as a blank column between two tables is a great trick and I am sure it could be turned into multiple blank columns by adding dummy variables.
Ask a Question
Discussion stats
  • 2 replies
  • 267 views
  • 0 likes
  • 2 in conversation