## proc report

Frequent Contributor
Posts: 87

# proc report

Hi, I have a proc report that produces the below report except the last line "Total without XXX". Please help me compute that last line. Basically, all I need is to summarize the values without considering XXX numbers.

Total without XXX = Total - XXX

 var1 var2 var3 aaa c 52 aaa 52 XXX a 678 b 126 c 118 d 46 e 164 f 13 XXX 1,145 CCC a 1,056 b 642 c 1,368 d 596 e 694 f 21 CCC 4,377 y a 223 b 222 y 445 Total 6,019 Total without XXX 4874
Frequent Contributor
Posts: 76

## Re: proc report

data Have;

input var1 \$ var2 \$ var3;

cards;

aaa c 52

XXX a 678

XXX b 126

XXX c 118

XXX d 46

XXX e 164

XXX f 13

CCC a 1056

CCC b 642

CCC c 1368

CCC d 596

CCC e 694

CCC f 21

y a 223

y b 222

;

proc report data = Have out = Have1;

define var1/group;

define var2/group;

break after var1/summarize;

rbreak after/summarize;

run;

proc sql;

select var3 into :xxxcnt from Have1 where var1 = "XXX" and _break_ = "var1";

select var3 into :totcnt from Have1 where var1 = " ";

quit;

data Want;

length var1 \$ 20;

set Have1 end = eof;

if var1 = " " then var1 = "Total";

output;

if eof then do;

var1 = "Total Without XXX";

var3 = &totcnt - &xxxcnt;

output;

end;

drop _break_;

run;

proc report;run;

Super User
Posts: 9,599

## Re: proc report

Well, you can just create the dataset using aggregates, then proc report that (not tested):

proc sql;

create table WANT as

/* Get data */

select  VAR1,

VAR2,

VAR3,

ORD=1

from    HAVE

union all

/* Get subtotals */

select  VAR1,

VAR2,

sum(VAR3) as VAR3,

ORD=2

from    HAVE

group by VAR1,

VAR2

union all

/* Get total */

select  "Total" as VAR1,

"" as VAR2,

sum(VAR3) as VAR3,

ORD=99

from    HAVE

union all

/* Get total without XXX */

select  "Total without" as VAR1,

"" as VAR2,

sum(VAR3) as VAR3,

ORD=999

from    (select * from HAVE where VAR1 ne "XXX")

order by VAR1,

VAR2,

ORD;

quit;

Super User
Posts: 10,761

## Re: proc report

You'd better post it at ODS and Report forum, since it is a question about proc report. and Cynthia is there.

### Code: Program.sas

`data Have;input var1 \$ var2 \$ var3;cards;aaa c 52XXX a 678XXX b 126XXX c 118XXX d 46XXX e 164XXX f 13CCC a 1056CCC b 642CCC c 1368CCC d 596CCC e 694CCC f 21y a 223y b 222; proc report data = Have nowd out=x;define var1/group;define var2/group;define var3/analysis sum;compute var3; if _BREAK_='var1' and var1='XXX' then s=var3.sum;endcomp;compute after;var1='Total';dif=var3.sum-s;line 'Total without XXX:' dif best32.;endcomp;break after var1/summarize;rbreak after/summarize;run;`

Xia Keshan

Discussion stats
• 3 replies
• 303 views
• 1 like
• 4 in conversation