topic Re: Proc report by variable override group label in SAS Programming

Proc report by variable override group label

anandbillava — Tue, 06 Apr 2010 19:00:15 GMT

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.

Re: Proc report by variable override group label

data_null__ — Tue, 06 Apr 2010 20:44:49 GMT

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_.

Re: Proc report by variable override group label

anandbillava — Tue, 06 Apr 2010 23:19:55 GMT

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

Re: Proc report by variable override group label

Cynthia_sas — Wed, 07 Apr 2010 06:59:56 GMT

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]

Re: Proc report by variable override group label

anandbillava — Wed, 07 Apr 2010 20:43:04 GMT

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

Re: Proc report by variable override group label

Cynthia_sas — Thu, 08 Apr 2010 05:48:00 GMT

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

Re: Proc report by variable override group label

anandbillava — Fri, 09 Apr 2010 16:10:31 GMT

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

Re: Proc report by variable override group label

anandbillava — Thu, 22 Apr 2010 18:40:08 GMT

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

Re: Proc report by variable override group label

Cynthia_sas — Thu, 22 Apr 2010 18:55:57 GMT

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

Re: Proc report by variable override group label

anandbillava — Thu, 22 Apr 2010 19:34:47 GMT

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.

Re: Proc report by variable override group label

Cynthia_sas — Thu, 22 Apr 2010 20:20:53 GMT

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]

Re: Proc report by variable override group label

anandbillava — Thu, 22 Apr 2010 20:46:58 GMT

Thank you so much cynthia.
But incase if I must add group header for the subsequent pages if one group spills to different pages.

Re: Proc report by variable override group label

Cynthia_sas — Fri, 23 Apr 2010 04:53:37 GMT

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]

Re: Proc report by variable override group label

anandbillava — Tue, 27 Apr 2010 16:47:42 GMT

Thanks cynthia.. that works

Re: Proc report by variable override group label

anandbillava — Wed, 28 Apr 2010 20:47:06 GMT

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.;

Re: Proc report by variable override group label

Cynthia_sas — Wed, 28 Apr 2010 21:26:19 GMT

Hi:
That $300. is the FORMATTED length which should be used to write the variable BEFORE_LINE to the output file.

The maximum length might conflict with LINESIZE if you were using the LISTING destination, however, since you are not using the LISTING destination, you can make that number bigger...or you can use the $VARYING. format in your COMPUTE block:
[pre]
lg=length(before_line);
line before_line $varying. lg;
[/pre]

The other issue you could be running into is the implied length for before_line -- which you could control with a LENGTH statement in your COMPUTE block.
[pre]
compute before _page_;
length before_line $500;
...more code...
[/pre]

The LENGTH statement controls the internal storage size of the variable BEFORE_LINE, while the format size in the LINE statement controls the displayed size of the variable.

cynthia

Re: Proc report by variable override group label

anandbillava — Mon, 03 May 2010 14:55:29 GMT

Using startpage=no option in SAS 9.1.3 creates a weird problem when there is a single observation in a group and that starts in a new page. It does not prints the column headers rather it prints the group header twice. According to SAS tech support this issue is fixed in version 9.2

Re: Proc report by variable override group label

anandbillava — Mon, 17 May 2010 20:55:18 GMT

Hello ,
In this program I wanted to print different variable which is other than the group variable inside the compute before block.
But its printing blank. But if I use the compute before varialbe then it prints without any problem. But printing different column or varirable inside the compute before using line is results in blank.

Original,
compute before region / style=Header{just=l font_weight=bold}; before_line = catx(' ','Region:',trim(region)); line before_line $100.; endcomp;
Instead of region I want to use some other variable like below..
compute before region / style=Header{just=l font_weight=bold}; before_line = catx(' ','Region:',trim(some_other_variable other than region)); line before_line $100.; endcomp;

Can somebody throw some light here ?
Thanks

Message was edited by: anandbillava Message was edited by: anandbillava

Re: Proc report by variable override group label

Cynthia_sas — Tue, 18 May 2010 05:02:22 GMT

Hi:

PROC REPORT is not like a DATA step program. The variable you want to show or use in the COMPUTE before block may not be available to PROC REPORT. Remember that PROC REPORT has a couple of rules that control behavior:

1) Left to Right rule: PROC REPORT builds each report row from left to right, as variables are listed on the COLUMN statement. So, for example, in the COLUMN statement below:
COLUMN REGION SUBSIDIARY PRODUCT SALES RETURNS;

There is no visibility of SUBSIDIARY or PRODUCT inside the COMPUTE block for REGION.

2) Every variable you use in a PROC REPORT step MUST be in the COLUMN statement or must be a temporary variable. So, for example, if you had this COLUMN statement:
COLUMN REGION PRODUCT SALES RETURNS;

Even if SUBSIDIARY was in the input dataset, if it is not even listed in the COLUMN statement, it will not be available to you for testing or using in a LINE statement.

I do not know which of these situations you are encountering, but I suspect that one of these rules is what is causing the blanks to appear. Refer to this paper for some ideas of how to accomplish different break processing.
http://support.sas.com/rnd/papers/sgf07/sgf2007-report.pdf

cynthia

Re: Proc report by variable override group label

anandbillava — Tue, 18 May 2010 13:42:24 GMT

I will go through that.

I have one more things in the above program can I print the group headers which is printed inside the compute block also in starting of each page. Now this is not prinitng when group spills into new page. Group prints only once.

I used by varialbe and other techniques but no luck yet. It prints group header when i use byval but it encounters different error. Accrodding to SAS this is a bug and its fixed in version 9.2.

The bug is when there is only one observation for a group and if it had to start from a new page then groups are printed twice.