DATA Step, Macro, Functions and more

Proc report by variable override group label

Reply
Contributor
Posts: 72

Proc report by variable override group label

When using proc report with by variable each group prints with label Group=. Is it possible to avoid this. I want to have just the variable value.
Respected Advisor
Posts: 3,777

Re: Proc report by variable override group label

Look at the title statement for the #BYVAR #BYVAL directives.

Another alternative would be to include the variable in the COLUMN statement and print it with COMPUTE before _PAGE_.
Contributor
Posts: 72

Re: Proc report by variable override group label

But i do not want each group to be printed in different page. I tihnk using byval. byvar and computer techniques will result in each group to be printed in different page
SAS Super FREQ
Posts: 8,744

Re: Proc report by variable override group label

Hi:
Using a BY statement will automatically cause PROC REPORT to start every BY group at the beginning of a new page. So if your BY group will all fit on one page, then if you have 3 by groups, you would have a 3 page report. BY group processing does not work any differently when you use #BYVAL/#BYVAR or not. You can see from the #1 report and #2 report below that each report contains 3 pages -- because of the BY statement.

The difference is that if you compare report #3, without a BY statement, to the other 2 reports, this report has only 1 page (as compared to 3 pages). The BREAK statement still does a summary for each region, but since there is no BY statement, each region is on the same page. The empty line between regions comes from the LINE statement in the COMPUTE block for REGION.

The side effect of a BY statement is that you get every BY group on a separate page -- that's why the use of #BYVAL works in a TITLE -- because the BY information is available to be used.

If you further look at the #4 report -- you will see how a COMPUTE BEFORE can be used to put a header above every REGION value.

cynthia
[pre]
options nodate pageno=1;

proc sort data=sashelp.shoes out=shoes;
by region product;
run;

ods listing;
ods pdf file='useby1.pdf';
ods rtf file='useby1.rtf';
ods html file='useby1.html' style=sasweb;

proc report data=shoes nowd;
by region;
title '1) use by statement';
where region in ('Asia', 'Pacific', 'Canada');
column product sales inventory returns;
define product / group;
define sales / sum;
define inventory / sum;
define returns/ sum;
rbreak after / summarize;
run;
ods _all_ close;

options nodate pageno=1 nobyline;
ods listing;
ods pdf file='useby2.pdf';
ods rtf file='useby2.rtf';
ods html file='useby2.html' style=sasweb;

proc report data=shoes nowd;
by region;
title '2) use BYVAL statement for #byval1';
where region in ('Asia', 'Pacific', 'Canada');
column product sales inventory returns;
define product / group;
define sales / sum;
define inventory / sum;
define returns/ sum;
rbreak after / summarize;
run;
ods _all_ close;
options byline;


options nodate pageno=1;
ods listing;
ods pdf file='noby3.pdf';
ods rtf file='noby3.rtf';
ods html file='noby3.html' style=sasweb;

proc report data=shoes nowd;
title '3) No BY statement in this proc report';
where region in ('Asia', 'Pacific', 'Canada');
column region product sales inventory returns;
define region / group;
define product / group;
define sales / sum;
define inventory / sum;
define returns/ sum;
break after region / summarize;
compute after region ;
line ' ';
endcomp;
run;
ods _all_ close;

options nodate pageno=1;
ods listing;
ods pdf file='noby4.pdf';
ods rtf file='noby4.rtf';
ods html file='noby4.html' style=sasweb;

proc report data=shoes nowd;
title '4) No BY statement -- use COMPUTE BEFORE';
where region in ('Asia', 'Pacific', 'Canada');
column region product sales inventory returns;
define region / group noprint;
define product / group;
define sales / sum;
define inventory / sum;
define returns/ sum;
break after region / summarize;
compute after region ;
line ' ';
endcomp;
compute before region /
style=Header{just=l font_weight=bold};
before_line = catx(' ','Region:',trim(region));
line before_line $100.;
endcomp;
run;
ods _all_ close;

[/pre]
Contributor
Posts: 72

Re: Proc report by variable override group label

Thanks cynthia,

But in my case the group headers printed as title for each group (may be in same page depending up on the size of the group). If i use the method mentioned by you the group headers are printed inside the table and after each group.
My condition is
Group headers should be printed on top of each group not at the bottom and not inside the table.
Is there any other way you suggest.
Anand
SAS Super FREQ
Posts: 8,744

Re: Proc report by variable override group label

Hi:
The only 2 alternatives would be to
--a) use BY group processing and use the #BYVAL in the title, as shown in report #2 above -- this had the effect of having the BY value in the TITLE (which would be outside the table and in the title area) in this way, the #BYVAL in the title would be a "group header", but outside the table and at the "top" of each group
or
--b) use PROC REPORT and COMPUTE BEFORE _PAGE_-- but this would put the information for the GROUP inside the box of the table, which you said you didn't want.

I guess I do not understand what you mean by the "group headers are printed inside the table and after each group". In my code examples, the "BREAK AFTER REGION is causing a summary line to be placed at the bottom of each group. But whether you look at report #2 or report #4, the name of the region is always placed -above- the report rows for the region.

cynthia
Contributor
Posts: 72

Re: Proc report by variable override group label

Thanks Cynthia...
I got solution for many of my problems with your suggestion. I appreciate.
Thanks once again.
Contributor
Posts: 72

Re: Proc report by variable override group label

HI Cynthia,
I am trying to add headers for each of the group, but at the same time when it encounters new page It should not print header twice.
Please let me know if you have any idea.
Anand
SAS Super FREQ
Posts: 8,744

Re: Proc report by variable override group label

Hi:
I am having a hard time envisioning what you mean by "add headers for each of the group" -- generally, when you use PROC REPORT, column headers -do- repeat at the top of every page in a paged destination, such as RTF or PDF. Can you post a sample program (using sashelp.shoes or sashelp.cars) that illustrates your issue. Please also describe your ODS destination of interest (RTF, PDF or HTML).

cynthia
Contributor
Posts: 72

Re: Proc report by variable override group label

data shoes;
set sashelp.shoes;
run;

ods pdf file='c:\1.pdf';
proc report data=shoes split=' ' nocenter nowd style(REPORT)={cellspacing=0 cellpadding=2 background=white}
style(HEADER)={foreground=black font_weight=bold font_size = 1 just=center}
style(COLUMN)={foreground=black font_size = 1};
COLUMN region product subsidiary stores sales;
define region / group noprint;
DEFINE edcC / DISPLAY 'EDC' ;
DEFINE product / DISPLAY 'EDC' ;
DEFINE subsidiary / DISPLAY 'EDC' ;
DEFINE stores / DISPLAY 'EDC' ;
DEFINE sales / DISPLAY 'EDC' ;

compute before region / style=Header{just=l font_size = 1 font_weight=bold background=wheat};
before_line = catx(' ',trim(region));
line before_line $300.;
endcomp;
run;

ods pdf close ;


The above program writes output to a ODS destincation.
You can see in the same page there are multiple groups printed.
But the column headers are printed only once at the starting of the page and its not printed for each of the group if group starts in between pages.
I dont wnat skip the pages when group does not have more observation to print in that page.
Hopefuly this will help you in understanding.
SAS Super FREQ
Posts: 8,744

Re: Proc report by variable override group label

Hi:
To do what you want, you need to make separate tables for each group. This is going to require either a BREAK statement with the PAGE option (Approach 1) or using BY group processing (Approach 2). Luckily for you, ODS PDF has the STARTPAGE=NO option, which causes normal "page break" behavior to be suppressed. So, where you would normally get a page break after or before each group with either of these techniques, the STARTPAGE=NO option will suppress the page break. Perhaps one of these 2 outputs is more what you wanted to get.

Please note that Approach 2 does require that the data is sorted for BY group processing and, if you want to suppress the normal BY line, you have to use the NOBYLINE option BEFORE your PROC REPORT step. Also note that with this approach, you may notice some difference in the overall table width because each group's table will be formatted separately (this means that the table for Africa may be a different width than the table for Asia, etc). If you want all the output tables to be the same width, then you would need to use the outputwidth= or width= option on your STYLE(REPORT) override:
[pre]
style(REPORT)={cellspacing=0 cellpadding=2 background=white outputwidth=3in}
[/pre]

cynthia
[pre]
** Approach 1 -- use BREAK combined with STARTPAGE;
ods pdf file='c:\temp\use_startpage.pdf' startpage=no;
proc report data=sashelp.shoes split=' ' nocenter nowd
style(REPORT)={cellspacing=0 cellpadding=2 background=white}
style(HEADER)={foreground=black font_weight=bold font_size = 1 just=center}
style(COLUMN)={foreground=black font_size = 1};
title 'Approach 1';
COLUMN region product subsidiary stores sales;
define region / group noprint;
DEFINE product / DISPLAY 'EDC' ;
DEFINE subsidiary / DISPLAY 'EDC' ;
DEFINE stores / DISPLAY 'EDC' ;
DEFINE sales / DISPLAY 'EDC' ;
break after region / page;
compute before region / style=Header{just=l font_size = 1 font_weight=bold background=wheat};
before_line = catx(' ',trim(region));
line before_line $300.;
endcomp;
run;

ods pdf close ;


** Approach 2 use BY group with STARTPAGE;
proc sort data=sashelp.shoes out=shoes;
by region;
run;

options nobyline;
ods pdf file='c:\temp\use_startpage_bygroup.pdf' startpage=no uniform;
proc report data=shoes split=' ' nocenter nowd
style(REPORT)={cellspacing=0 cellpadding=2 background=white}
style(HEADER)={foreground=black font_weight=bold font_size = 1 just=center}
style(COLUMN)={foreground=black font_size = 1};
title 'Approach 2';
by region;
COLUMN region product subsidiary stores sales;
define region / group noprint;
DEFINE product / DISPLAY 'EDC' ;
DEFINE subsidiary / DISPLAY 'EDC' ;
DEFINE stores / DISPLAY 'EDC' ;
DEFINE sales / DISPLAY 'EDC' ;
compute before region / style=Header{just=l font_size = 1 font_weight=bold background=wheat};
before_line = catx(' ',trim(region));
line before_line $300.;
endcomp;
run;

title;
options byline;
ods pdf close ;

[/pre]
Contributor
Posts: 72

Re: Proc report by variable override group label

Thank you so much cynthia.
But incase if I must add group header for the subsequent pages if one group spills to different pages.
SAS Super FREQ
Posts: 8,744

Re: Proc report by variable override group label

Hi:
In SAS 9.2, using COMPUTE BEFORE _PAGE_ instead of COMPUTE BEFORE REGION might give you the results you want. When I use the code below in SAS 9.2, the LINE statement output is written -above- the column headers even at the page break when using COMPUTE BEFORE _PAGE_.

I no longer have SAS 9.1.3 to test with, so I'm not sure the same code will produce these same results in SAS 9.1.3. There used to be issues with COMPUTE BEFORE _PAGE_ in some ODS destinations in earlier versions of SAS.

cynthia
[pre]
** Approach 1 -- use BREAK combined with STARTPAGE;
ods pdf file='c:\temp\use_startpage_page.pdf' startpage=no;
proc report data=sashelp.shoes split=' ' nocenter nowd
style(REPORT)={cellspacing=0 cellpadding=2 background=white outputwidth=3in}
style(HEADER)={foreground=black font_weight=bold font_size = 1 just=center}
style(COLUMN)={foreground=black font_size = 1};
title 'Approach 1';
COLUMN region product subsidiary stores sales;
define region / group noprint;
DEFINE product / DISPLAY 'EDC' ;
DEFINE subsidiary / DISPLAY 'EDC' ;
DEFINE stores / DISPLAY 'EDC' ;
DEFINE sales / DISPLAY 'EDC' ;
break after region / page;
compute before _page_ / style=Header{just=l font_size = 1 font_weight=bold background=wheat};
before_line = catx(' ',trim(region));
line before_line $300.;
endcomp;
run;

ods pdf close ;


** Approach 2 use BY group with STARTPAGE;
proc sort data=sashelp.shoes out=shoes;
by region;
run;

options nobyline;
ods pdf file='c:\temp\use_startpage_bygroup_page.pdf' startpage=no;
proc report data=shoes split=' ' nocenter nowd
style(REPORT)={cellspacing=0 cellpadding=2 background=white outputwidth=3in}
style(HEADER)={foreground=black font_weight=bold font_size = 1 just=center}
style(COLUMN)={foreground=black font_size = 1};
title 'Approach 2';
by region;
COLUMN region product subsidiary stores sales;
define region / group noprint;
DEFINE product / DISPLAY 'EDC' ;
DEFINE subsidiary / DISPLAY 'EDC' ;
DEFINE stores / DISPLAY 'EDC' ;
DEFINE sales / DISPLAY 'EDC' ;
compute before _page_ / style=Header{just=l font_size = 1 font_weight=bold background=wheat};
before_line = catx(' ',trim(region));
line before_line $300.;
endcomp;
run;
title;
options byline;
ods pdf close ;

[/pre]
Contributor
Posts: 72

Re: Proc report by variable override group label

Thanks cynthia.. that works
Contributor
Posts: 72

Re: Proc report by variable override group label

hi,
In The below statement what does $300 means.. Is there any maximum length for this. Because my group value breaks after some character.

line before_line $300.;
Ask a Question
Discussion stats
  • 25 replies
  • 1136 views
  • 0 likes
  • 3 in conversation