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 |
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;
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;
You'd better post it at ODS and Report forum, since it is a question about proc report. and Cynthia is there.
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.