topic Rows above header in PROC REPORT? in ODS and Base Reporting

Rows above header in PROC REPORT?

JackHamilton — Wed, 17 Dec 2008 16:55:03 GMT

Suppose I want to create a report using PROC REPORT and the ExcelXP tagset; the report should have data filters and a subtotal at the top.

That's straightforward to do if you want only one total:

=====
ods tagsets.ExcelXP
file='c:\temp\subtotal.xls'
options(
embedded_titles='yes'
row_repeat='4'
frozen_headers='4'
autofilter='all'
orientation='landscape');
ods listing close;

title 'Excel Subtotal';

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 + 7;
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 COMPUTE BEFORE _PAGE_ block lets me write a line above the column headers, and the SUBTOTAL formula returns the sum of the values in column 4 after filtering.

But suppose I want to have the total of the ACTUAL column in column 4, and the total of the PREDICT column in column 5; is there a way to do that? In other words, is there a way to write to a particular column in a COMPUTE BEFORE _PAGE_ block?

Also, if I have two LINE statements in the compute block, only the first line is written; is this a bug, or expected behavior (expected by other people, that is)?

PS: How do I post code with indentation?

Re: Rows above header in PROC REPORT?

Cynthia_sas — Wed, 17 Dec 2008 20:13:23 GMT

Hi, Jack:
[ pre ] and [/ pre ] around your code (only without spaces).

Or to put it another way...just like the <pre> and </pre>, but with square brackets instead of < and >

Not sure about the rest of your questions without looking closer at the code.
cynthia

Re: Rows above header in PROC REPORT?

Cynthia_sas — Thu, 18 Dec 2008 07:43:56 GMT

Jack:
Did you notice how the output from the LINE statement spans all the columns at the top of the table? That's because the XML has this instruction caused by the LINE statement:
[pre]
<Cell ss:StyleID="NoteContent" ss:MergeAcross="4" .......
[/pre]

So, there's no way to place numbers in column 4 or column 5 inside the LINE statement because the line is automatically spanning or merging multiple columns.

The best I could do was COMPUTE BEFORE and COMPUTE AFTER. Maybe you could assign a formula using TAGATTR. But the thing is that the BREAK BEFORE/COMPUTE BEFORE location is NOT the same as the COMPUTE BEFORE _PAGE_ location and it might not be what you want.

In the code below, I simplified the text in the LINE at the top.

cynthia
[pre]
ods tagsets.ExcelXP
file='c:\temp\subtotal.xls'
options(
embedded_titles='yes'
row_repeat='4'
frozen_headers='4'
autofilter='all'
orientation='landscape');

ods listing close;

title 'Excel Subtotal';

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;
rbreak before / summarize;
compute before;
country2 = 'Total';
endcomp;
rbreak after / summarize;
compute after;
country2 = 'Total';
endcomp;
compute before _page_;
rows = n + 7;
line 'N=' N ' Rows (N+7) =' Rows;
endcomp;
run;

ods tagsets.excelxp close;
ods listing;
[/pre]

Re: Rows above header in PROC REPORT?

JackHamilton — Thu, 18 Dec 2008 18:51:26 GMT

Yes, a solution to this would require turning off cell merging (but merging cells is never a good idea anyway, in my opinion).

The cells with the subtotal formulas have to be above the filters, or the filters won't work right (and neither will the subtotals), so COMPUTE BEFORE and COMPUTE AFTER don't have the desired result.

Re: Rows above header in PROC REPORT?

JackHamilton — Tue, 30 Dec 2008 17:45:42 GMT

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.

Re: Rows above header in PROC REPORT?

Cynthia_sas — Tue, 30 Dec 2008 18:09:17 GMT

Jack:
Here's my idea -- take SAS out of the picture. This is what I mean:

1) Make an Excel spreadsheet (without SAS) by typing in dummy tables and numbers and the headers the way you want and autofiltering the way you want and -then- save that spreadsheet as a .XML file (XML 2003 -- NOT XML 2007).

2) Next, take the spreadsheet that you're getting from SAS and cut the data WAY down -- so that you have the top info and the 2nd table and the broken filtering.

Now, open both XML files in NOTEPAD and see in #1 where Excel wants the autofiltering XML instructions to be placed when SAS is out of the picture and then see what the difference is in #2 where SAS places the autofiltering tags. I'm guessing that there's some quirky tag or way to define autofiltering that Excel does that the current ExcelXP tagset is not putting in the same place as Excel lets you do manually. I have found this in a few cases that Excel will let me do something and save the file as XML but I can't get there with the ExcelXP tagset -- it was never a deal breaker for me, but I did discover that sometimes Excel puts tags in the file in places that ODS is not geared up for.

Of course, it would also be good to find whether native Excel will let you have the equivalent of 2 tables in one sheet and put will autofiltering on the 2nd table. If Excel does allow that (which you will find out when you do #1), then you will know how that XML is supposed to look. And then if your comparison of the 2 XML files reveals WHERE the tags for autofiltering are supposed to go and what the differences are, then you may need to customize the ExcelXP tagset to contain a custom event that writes out the autofiltering XML on the 2nd table only.

At least you'll find out whether it's possible before you go down the custom tagset road. That's my idea. (Eric may have a different one!)

cynthia