Help using Base SAS procedures

proc report

Reply
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

var1var2var3
aaac52
aaa 52
XXXa678
b126
c118
d46
e164
f13
XXX 1,145
CCCa1,056
b642
c1,368
d596
e694
f21
CCC 4,377
ya223
b222
y 445
Total 6,019
Total without XXX 4874
Contributor
Posts: 65

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
Super User
Posts: 7,430

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: 9,691

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

Ask a Question
Discussion stats
  • 3 replies
  • 274 views
  • 1 like
  • 4 in conversation