topic Re: proc report in SAS Procedures

proc report

helloSAS — Thu, 16 Jul 2015 03:13:50 GMT

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

Re: proc report

MadhuKorni — Thu, 16 Jul 2015 10:43:17 GMT

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;

Re: proc report

RW9 — Thu, 16 Jul 2015 10:52:49 GMT

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;

Re: proc report

Ksharp — Thu, 16 Jul 2015 12:16:49 GMT

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