topic Re: Can we do conditional break or rbreak in proc report in ODS and Base Reporting

Can we do conditional break or rbreak in proc report

sravanpathu — Sat, 24 Nov 2012 17:40:48 GMT

Can we do conditional break or rbreak in proc report

Ex:

A 5

B 5

C 6

D 4

E 3

-----

TOTAL 13

TOTAL INCLUDES ONLY "CDE"

Re: Can we do conditional break or rbreak in proc report

Cynthia_sas — Sat, 24 Nov 2012 23:30:00 GMT

Hi, not directly, but you can create a computed column on the report and then only add up numbers based on your condition (see #1). Then, you can move the new total to the original column (see #2). Or, you could make a variable in a DATA step and then it would simplify your PROC REPORT code a bit (see #3).

cynthia

data fakedata;
length grp $5.;
infile datalines;
input grp $ cnt;
return;
datalines;
A 5
B 5
C 6
D 4
E 3
;
run;

ods html file='c:\temp\calccol.html';
proc report data=fakedata nowd;
title '1) do conditional sum with CALCVAR computed item';
column grp cnt calcvar;
define grp /order;
define cnt / sum;
define calcvar / computed;
compute calcvar;
    calcvar = 0;
    if grp in ('C', 'D', 'E') then do;
       hold_sum + cnt.sum;
       calcvar = cnt.sum;
    end;
    if _break_ = '_RBREAK_' then do;
       calcvar = hold_sum;
    end;
endcomp;
rbreak after / summarize;
run;

proc report data=fakedata nowd;
title '2) do conditional sum and use NOPRINT';
title2 'Also, move calculated total to CNT column';
column grp cnt calcvar;
define grp /order;
define cnt / sum;
define calcvar / computed noprint;
compute calcvar;
    calcvar = 0;
    if grp in ('C', 'D', 'E') then do;
       hold_sum + cnt.sum;
       calcvar = cnt.sum;
    end;
    if _break_ = '_RBREAK_' then do;
       calcvar = hold_sum;
    end;
endcomp;
rbreak after / summarize;
compute after / style={just=l};
      grp = 'Total';
      cnt.sum = hold_sum;
      line 'Total includes only C, D, E';
endcomp;
run;
ods html close;

** Alternate approach;
data fake2;
set fakedata;
if grp in ('C', 'D', 'E') then
cnt_this = cnt;
else cnt_this = 0;
run;

ods html file='c:\temp\alt_approach.html';
proc report data=fake2 nowd;
title '3) Alternate approach use DATA step to make variable';
column grp cnt cnt_this;
define grp /order;
define cnt / sum;
define cnt_this / sum noprint;
rbreak after / summarize;
compute after / style={just=l};
      grp = 'Total';
      cnt.sum = cnt_this.sum;
      line 'Total includes only C, D, E';
endcomp;
run;
ods html close;

Re: Can we do conditional break or rbreak in proc report

sravanpathu — Tue, 27 Nov 2012 01:31:37 GMT

Thanks a lot Cynthia. It is Very use full.

like above

I had the count of 12 months.

I had across-ed the month variable.

Then the output will be like below

Mar-12 Apr-12 Jun-12

count amount count amount count Amount

Like above i had the output.

New Requirement is i want total that contains only A B D only

How to use the same code for the above.

I had built the code using your 3 approach.

I had succeeded in that but i need using 2 approach kindly help me.

am facing problem in compute block.

when am using variable.sum

am getting error that it is an illegal

kindly help

Re: Can we do conditional break or rbreak in proc report

Cynthia_sas — Tue, 27 Nov 2012 03:25:39 GMT

Hi:

You will want to read about ACROSS variables and PROC REPORT. When you use ACROSS variables, PROC REPORT assigns absolute column numbers to each ACROSS item and the variables that are under each ACROSS item. That means you cannot use "variable.sum" in your COMPUTE blocks if your items are under ACROSS variables. The reference "VARIABLE.SUM" is incorrect in an ACROSS scenario, as your error message indicates.

There have been many previous forum postings on using ACROSS items. For example, if you refer to this paper on PROC REPORT (http://support.sas.com/resources/papers/ProcReportBasics.pdf - page 6) or this paper (http://support.sas.com/rnd/papers/sgf07/sgf2007-report.pdf - page 12), you will see how the ABSOLUTE column names are formed by PROC REPORT. And, the program below does something silly (adds height and weight together), but you get the idea of the reference syntax you have to use. Before you modify your program, it will help if you you understand the code in the example below and in the papers.

You said that you wanted a count for A, B and D only -- that logic would be the same as what I showed for C, D and E. However, you did not say whether you wanted a sum on AMOUNT or on COUNT or on both items. You did not show examples of your data, so it will be hard for anyone to try to help you, since in order to come up with a working program, they will have to do what I did and make some "fake" data that now includes an AMOUNT variable and some MONTH variables.

I am going to be away from my computer, teaching this week, so I will not be able to spend more time on this, but perhaps other folks might be able to help you if you study the papers and program below, come up with some code and then, post your code with sample data. My example showed how to write a program to generate some "fake" data. Since you didn't explain in detail what your data looked like or post any code that showed you were using ACROSS item(s), my program did not utilize any ACROSS items.

Hopefully, this will be enough info to get you started. The program below produced the output in the screenshot.

cynthia

ods html file='c:\temp\calc_across.html';

proc report data=sashelp.class nowd;

where age in (11,12,13);

column age sex,(height weight calc);

define age / group;

define sex / across;

define height / sum;

define weight / sum;

define calc / computed;

compute calc;

_c4_ = _c2_ + _c3_;

_c7_ = _c5_ + _c6_;

endcomp;

run;

ods html close;

Re: Can we do conditional break or rbreak in proc report

sravanpathu — Tue, 27 Nov 2012 15:07:38 GMT

Thanks alot.............!

I read the PDF its very helpful.

Am succeeded using absolute column.(_c2_ _c3_ like)