proc report

[helloSAS]

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

[MadhuKorni]

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;

[RW9]

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;

[Ksharp]

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