I'm trying something different now - a separate PROC REPORT to create the headers. That way I can have multiple cells. Here's some sample code, still a work in progress:
=====
options nocenter;
ods tagsets.ExcelXP
file='c:\temp\subtotal.xls'
options(
embedded_titles='yes'
row_repeat='7'
frozen_headers='7'
autofilter='no'
sheet_interval='none'
orientation='landscape'
);
ods listing close;
title 'Excel Subtotal';
proc sql noprint;
create table groups as
select distinct
country,
region,
product,
sum(actual) as actual,
sum(predict) as predict
from
sashelp.prdsale
where
actual > 500
group by
country,
region,
product
;
create table rows as
select
count(*) as rows
from
groups;
select
rows
into
:ROWS
from
rows;
quit;
data empty_groups;
output;
stop;
set groups;
run;
proc report data=empty_groups nofs missing nocenter split='!';
define country / 'Selected!Records';
define region / '';
define product / '';
define actual / 'Sum of!Actual';
define predict / 'Sum of!Predict';
compute region;
region = 'of ' || left(put(&ROWS., comma6.0));
endcomp;
run;
ods tagsets.ExcelXP
options(
embedded_titles='no'
autofilter='all'
);
proc report data=sashelp.prdsale nofs missing nocenter;
where actual >= 500;
column country region product
country=country2 region=region2 product=product2
actual predict n;
define country / group noprint;
define region / group noprint;
define product / group noprint;
define country2 / group;
define region2 / group;
define product2 / group;
define actual / sum;
define predict / sum;
define n / noprint;
compute before _page_;
rows = n + 9;
line '="Total of ACTUAL in selected records: " & text(subtotal(9,r5c4:r' rows z5. 'c4), "$ #,##0.00")';
endcomp;
run;
ods tagsets.excelxp close;
ods listing;
options noxwait noxsync;
x 'start c:\temp\subtotal.xls';
=====
The problem is that the AUTOFILTER is being applied to the first proc report and not the second, but I want just the opposite - I want the first, single-row PROC REPORT to not have any filters, and the second, multiple-data-row PROC REPORT to have filters.
How do I do that?
After that problem is solved, I will add compute blocks to the first PROC REPORT to place the appropriate formulas into the cells. But there's no point in doing that if I can't get the filters moved to the right place.
