topic Add total to first row in SAS Programming

Add total to first row

bollurajkumar — Wed, 05 Dec 2018 08:27:42 GMT

Hai all ,
I have a data like this.

A. B
World 25
India 30
USA 40
Pak 70

I want sum of total into world b column
Like
World 165
India 30
USA 40
Pak 70
Thanks in adv

Re: Add total to first row

PeterClemmensen — Wed, 05 Dec 2018 08:30:26 GMT

In a data set or report?

Re: Add total to first row

PeterClemmensen — Wed, 05 Dec 2018 09:09:48 GMT

In either case, you can do something like this

data have;
input A $ B;
datalines;
World 25
India 30
USA 40
Pak 70
;

proc sql;
   create table want as
   select 'World' as A, sum(B) as B from have
   union
   select * from have where A ne 'World'
   order by B descending;
quit;

Re: Add total to first row

singhsahab — Wed, 05 Dec 2018 09:16:33 GMT

Hi,

data have;
input  A $ B;
datalines;
World 25
India 30
USA 40
Pak 70
;
run;

proc report data=have;
column a b;
define a/order descending;
define a/order;
rbreak before /summarize;
run;

DATA WANT;
SET HAVE;
RETAIN S 0;
S=S+B;
if a='World' then call sortn(s,b);
else call sortn(b,s);
drop s;
RUN;

Thanks 🙂

Re: Add total to first row

Kurt_Bremser — Wed, 05 Dec 2018 09:28:08 GMT

I had a slightly different take on the SQL:

proc sql;
create table want as
select
  a,
  case
    when a = 'World' then (select sum(b) from have)
    else b
  end as b
from have;
quit;

Re: Add total to first row

Jagadishkatam — Wed, 05 Dec 2018 10:07:15 GMT

By data step

data have;
input  A $ B;
datalines;
World 25
India 30
USA 40
Pak 70
;
run;

data want;
set have;
if a='World' then sort=1;
else sort=0;
call sortn(sort,b);
run;

proc sort data=want;
by sort;
run;

DATA WANT2;
SET want;
by sort;
S+B;
if a='World' then b=s;
else b=b;
drop s;
RUN;

proc sort data=want2;
by sort;
run;

Re: Add total to first row

novinosrin — Wed, 05 Dec 2018 10:48:38 GMT

data have;
input A $ B;
datalines;
World 25
India 30
USA 40
Pak 70
;


data _null_ ;
if _n_=1 then do;
   declare hash H (ordered:'a') ;
   h.definekey  ("_n_") ;
   h.definedata ("a", "b") ;
   h.definedone () ;
   end;
set have end=lr;
rc=h.add();
k+b;
if lr;
_n_=1;
h.find();
b=k;
h.replace();
h.output(dataset:'want');
run;

Re: Add total to first row

novinosrin — Wed, 05 Dec 2018 11:02:34 GMT

More fun

data have;
input A $ B;
datalines;
World 25
India 30
USA 40
Pak 70
;
data _null_ ;
if _n_=1 then do;
   declare hash H (ordered:'a') ;
   h.definekey  ("_n_") ;
   h.definedata ("a", "b") ;
   h.definedone () ;
   end;
do _n_=nobs to 1 by -1;
set have nobs=nobs point=_n_;
k+b;
if _n_=1 then b=k;
rc=h.add();
end;
h.output(dataset:'want');
stop;
run;

Re: Add total to first row

novinosrin — Wed, 05 Dec 2018 19:36:28 GMT

And more fun:


data have;
input A $ B;
datalines;
World 25
India 30
USA 40
Pak 70
;


proc sql;
create table want as
select a, ifn(monotonic()=1,sum(b),b) as b
from have
order by monotonic();
quit;