The SAS Output Delivery System and reporting techniques

proc report BY group issue

Reply
N/A
Posts: 0

proc report BY group issue

hi all,

I have an issue with proc report output;

I am using BY group processing as follows:

********************************************************;
options nodate nonumber;

ods pdf notoc uniform file='c:/test.pdf';

option NoByLine ;

proc report data=test nowd style(report)={rules=none};
by location ckdate;

**column statement and several define statements follow here******;

compute before _page_/left;

line 'Location ' location $;

endcomp;

run;
****************************************************************************;

The text in compute block needs to be repeated for each and every page;
In actual output though, whenever there are multiple pages of data within a BY group, text is printed just once , for each BY group;

I tried using a title statement to print out location , but again I face the same issue;

is there a way to make it print for every page?


thanks;
Shankar;
SAS Super FREQ
Posts: 8,743

Re: proc report BY group issue

I believe you are encountering this condition:
http://support.sas.com/techsup/unotes/SN/007/007887.html
"PROC REPORT does not repeat values of GROUP/ORDER variables when groups break across pages"

At the present time, the workaround described in the Tech Support note is your only option.

cynthia
N/A
Posts: 0

Re: proc report BY group issue

Thanks Cynthia, that was useful information;

Now, I am stuck with another issue with my proc report code;
now my compute blocks are getting execcuted on every page (incorrectly);

***************************************************;
ods pdf notoc file='c:/test.pdf';
ods escapechar='~';
option NoByLine;

proc report data=test nowd;
by location planyear sc_acctype ReimbDate;

column location planyear sc_acctype ReimbDate chkNum ReimbAmt;

define location / group format=$35. noprint;


define planyear / group noprint;


define sc_acctype / group noprint;


define ReimbDate / display ;

define chkNum / order=data;

define ReimbAmt /order=data;

compute after sc_acctype;
x=ReimbAmt.sum; *sum of all amounts in this account ;
l ine 'account total is' x;
endcomp;

compute after planyear;
y=sum(x); *sum of all amounts under all accounts in this plan year;
line 'plan year total is' y;
endcomp;

compute after location;
z=sum(y); **sum of all amounts for all plan years falling under this location;
line 'location total is' y;
endcomp;

run;

**************************************************;
I would like the compute block for location to be executed only when location changes;
When location changes the compute block should print out a line with sum of amounts for all plan years for that location;

with my code above the compute block gets executed on each and every page!..I have combed through archives of both this forum and sas-l..but I am just not able to see where I got it wrong..

any insight would be greatly appreciated..

thanks,
Shankar
SAS Super FREQ
Posts: 8,743

Re: proc report BY group issue

Hi:
BY group processing, in general, changes the way that some procedures -- PROC REPORT, PROC PRINT and PROC TABULATE treat the data. In most cases, when you use BY group processing, you are telling the procedure to treat each by group as a separate entity -- isolated from the rest of the BY groups. This may be what's happening to your output. Sometimes BY group processing can force unwanted page breaks in your output or produce unexpected results.

Usually I find that BY group processing with PROC REPORT is not necessary, if I use BREAK processing effectively. So, that makes me wonder why you're using BY group processing instead of using BREAK processing:
[pre]
break after sc_acctype /summarize;
break after planyear /summarize;
break after location /summarize page;
[/pre]

These 3 break statements would give you a separate summary line for each break variable. PROC REPORT will also put every location on a separate page if you use the PAGE option on your BREAK statement. And, I also wonder why you're not using ReimbAmt.sum in your LINE statements.

If you are having trouble with your date fields showing in the correct date order, you might try ORDER=INTERNAL, as described here:
http://support.sas.com/faq/005/FAQ00582.html

Here's some dummy data (a small amount) and some PROC REPORT steps to compare.
[pre]
** first, make some data;
** and make the numbers small enough to add up in your head;
** to make sure that the numbers are all correct in the PROC REPORT totals;
data testdata;
length country $20 year 8 type $20 date 8 amount 8;
infile datalines;
input country $ year type $ date : date9. amount;
return;
datalines;
US 2006 XXX 01Jan2006 111
US 2006 XXX 01Jan2006 111
US 2006 XXX 01Feb2006 222
US 2006 XXX 01Feb2006 222
US 2006 YYY 01Jan2006 333
US 2006 YYY 01Jan2006 333
US 2006 YYY 01Feb2006 444
US 2006 YYY 01Feb2006 444
FR 2006 XXX 01Jan2006 111
FR 2006 XXX 01Jan2006 111
FR 2006 XXX 01Feb2006 222
FR 2006 XXX 01Feb2006 222
FR 2006 YYY 01Jan2006 333
FR 2006 YYY 01Jan2006 333
FR 2006 YYY 01Feb2006 444
FR 2006 YYY 01Feb2006 444
US 2007 XXX 01Jan2007 555
US 2007 XXX 01Jan2007 555
US 2007 XXX 01Feb2007 666
US 2007 XXX 01Feb2007 666
US 2007 YYY 01Jan2007 777
US 2007 YYY 01Jan2007 777
US 2007 YYY 01Feb2007 888
US 2007 YYY 01Feb2007 888
FR 2007 XXX 01Jan2007 555
FR 2007 XXX 01Jan2007 555
FR 2007 XXX 01Feb2007 666
FR 2007 XXX 01Feb2007 666
FR 2007 YYY 01Jan2007 777
FR 2007 YYY 01Jan2007 777
FR 2007 YYY 01Feb2007 888
FR 2007 YYY 01Feb2007 888
;
run;

options missing=' ' nodate nonumber byline;
ods listing close;

** see what regular break processing looks like;
ods pdf file='c:\temp\grp_order.pdf';
proc report data=testdata nowd
style(summary)={font_weight=bold};
title 'What BREAK processing totals look like (no BY)';
column country year type date prtdate amount;
define country / group;
define year /group;
define type /group;
define date /order format=date9. order=internal;
define prtdate /computed format=date9.;
define amount / sum;
break after country /summarize page;
break after year /summarize;
break after type /summarize;
compute before date;
holddate = date;
endcomp;
compute prtdate;
if _BREAK_ eq ' ' then prtdate=holddate;
endcomp;
run;
ods pdf close;

** now use LINE statements and BREAK processing;
ods pdf file='c:\temp\grp_order2.pdf';
proc report data=testdata nowd
style(lines)={just=r font_weight=bold};
title 'Using LINE statement at BREAK';
title2 'Using amount.sum in a compute block ';
column country year type date prtdate amount;
define country / group ;
define year /group ;
define type /group ;
define date /order format=date9. order=internal;
define prtdate /computed format=date9.;
define amount / sum;
break after country / page;
compute before date;
holddate = date;
endcomp;
compute prtdate;
if _BREAK_ eq ' ' then prtdate=holddate;
endcomp;
compute before _page_ /
style={just=c};
line 'Country = ' country $2.;
endcomp;
compute after country;
line 'Country ' country $2. ' Total: ' amount.sum comma12.;
endcomp;
compute after year;
line 'Year ' year 4. ' Total: ' amount.sum comma12.;
endcomp;
compute after type;
line 'Type ' type $3. ' Total: ' amount.sum comma12.;
endcomp;
run;
ods pdf close;
[/pre]

The first program uses standard PROC REPORT break processing -- but no BY group processing. The output shows every country on a separate page because of the PAGE option on the BREAK statement. Also, note how I use DATE as an order variable, but make a second "date" column called PRTDATE that displays the date on every line. This allows me to order by date, have the actual date value appear on every line and avoid getting these NOTE or ERROR messages in the log:
ERROR: You can only BREAK on GROUPing and ORDERing variables.
NOTE: Groups are not created because the usage of DATE is DISPLAY.

I define DATE as an ORDER variable and use a COMPUTE BEFORE block to grab the value of DATE.

I did not use NOPRINT on any of the variables so you could see that the LINE statement was being written correctly. Perhaps an approach like this is close to what you are trying to achieve.

cynthia
N/A
Posts: 0

Re: proc report BY group issue

hi Cynthia,
thanks again for the nice tips..
I tried several ideas based on brake and line statements and I am almost close to what I want..

the reason I was using BY group was the ability to use #byval in title lines (which I need for every page, even when there are multiple pages of data in one group)..
using compute before _page_ , those lines were not repeating on every page..

so now, I am using just one variable in BY group (date)..the report is very close to my requirements except that, for multiple pages of data for a given date, lines in compute before _page_ won't get executed..I tried the SAS notes idea (forcing a page break) --but that opened another bug, more serious..with that approach , I would have compute blocks (for location etc), being repeated when they should not..i.e, for multiple pages of data for a date, the compute block which calculates sum, should get executed just once, at the end of data..

I have tried data _null_ approach (ODS file print) but that did not go very far as well..

thanks again for all your help,
Shankar
Contributor
Posts: 49

Re: proc report BY group issue

Shankar,

If you're using BY groups to use the #BYVAL functionality, I would suggest instead that you create the text you want to appear in the report as a title as another variable (set it's attributes in the DISPLAY statetment to NOPRINT) and reference this in the COMPUTE BEFORE _PAGE_.

I prefer this to #BYVAL myself.

Cheers,
Lawrence
N/A
Posts: 0

Re: proc report BY group issue

Thanks for the suggestion Lawrence..

I tried that approach but the issue remains the same..when the data runs over multiple pages (for a given location, location is a group variable), the text in compute before _page_ appears only once, on first of those pages..

thanks,
Shankar
Ask a Question
Discussion stats
  • 6 replies
  • 819 views
  • 0 likes
  • 3 in conversation