BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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.
2 REPLIES 2
Cynthia_sas
SAS Super FREQ
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]
deleted_user
Not applicable
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.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 1273 views
  • 0 likes
  • 2 in conversation