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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.