topic Re: SUM of column in SAS Programming

SUM of column

yudhishtirb — Fri, 22 Jul 2016 07:09:58 GMT

Hello Team,

Greetings

I am new to SAS development so subject might be wrong. I am looking for assistance for below

There is one existing program and below is output of univariate statement.

Code	ClientName	Balance
2	ABC	31646302.55
2	ABC	1852401.43
2	ABC	51067.47
2	ABC	626316.68
2	ABC	3542.8
2	ABC	1763759.37
2	ABC	18908152.99
2	ABC	64965.59
2	ABC	8460524.72
2	ABC	18094.11

Now we need to add 1 more column as below

Code	ClientName	Balance	E1 New Column Required
2	ABC	31646302.55	31646302.55/SUM(Balance)
2	ABC	1852401.43	1852401.43/SUM(Balance)
2	ABC	51067.47	51067.47/SUM(Balance)
2	ABC	626316.68	626316.68/SUM(Balance)
2	ABC	3542.8	3542.8/SUM(Balance)
2	ABC	1763759.37	1763759.37/SUM(Balance)
2	ABC	18908152.99	18908152.99/SUM(Balance)
2	ABC	64965.59	64965.59/SUM(Balance)
2	ABC	8460524.72	8460524.72/SUM(Balance)
2	ABC	18094.11	18094.11/SUM(Balance)

E1 = value in balace column/Sum of all values in balance column

Can someone pleasse guide me which operation should I use to achieve above output. Original code is pasted below

proc univariate data=strat.master noprint;
var cursettlement;
by wclientcode wgroup;
output out=strat.rep sum=balance nobs=no_accs;
run;

Thanks In Advance

Re: SUM of column

RW9 — Fri, 22 Jul 2016 08:24:44 GMT

Add a step after the univariate:

proc sql;
  create table WANT as
  select  *,
          BALANCE / sum(BALANCE) as NEW_COLUMN
  from    STRAT.REP
  group by CODE,
           CLIENTNAME;
quit;

Re: SUM of column

Ksharp — Fri, 22 Jul 2016 08:25:31 GMT

It is easy for SQL.




data have;
infile cards expandtabs truncover;
input Code	ClientName $	Balance;
cards;
2	ABC	31646302.55
2	ABC	1852401.43
2	ABC	51067.47
2	ABC	626316.68
2	ABC	3542.8
2	ABC	1763759.37
2	ABC	18908152.99
2	ABC	64965.59
2	ABC	8460524.72
2	ABC	18094.11
;
run;
proc sql;
create table want as
 select *,Balance/sum(Balance) as want
  from have
   group by Code,ClientName;
quit;

Re: SUM of column

yudhishtirb — Fri, 22 Jul 2016 11:12:36 GMT

Hello All,

Thank you for feedback

But it is creating E1 as 1 always. It is not summing all values. Please look below sample

data have;
infile cards expandtabs truncover;
input Code	ClientName $	Balance;
cards;
2	ABC	10
2	ABC	20
2	ABC	30
;
run;

Then in output I must have

Code    Client  Balance  E1             E2
2	ABC	10       10/(10+20+30)  60
2	ABC	20       20/(10+20+30)  60
2	ABC	30       30/(10+20+30)  60


Can we do like this ?

Thanks In Advance

Re: SUM of column

DJongman — Fri, 22 Jul 2016 11:24:06 GMT

data have;
infile cards expandtabs truncover;
input Code	ClientName $	Balance;
cards;
2	ABC	10
2	ABC	20
2	ABC	30
;
run;

proc sql;
create table want as
 select *,Balance/sum(Balance) as E1, sum(Balance) as E2
  from have
   group by Code,ClientName;
quit;

Should do the trick.

And if you want to sort the result as in your sample:

proc sql;
create table want as
 select *,Balance/sum(Balance) as E1, sum(Balance) as E2
  from have
  
   group by Code,ClientName
   order by Code,ClientName,Balance;
quit;

Re: SUM of column

yudhishtirb — Fri, 22 Jul 2016 11:58:39 GMT

Hi,

Thanks for feedback.

Sorry, I posted wrong input. Please consider below input

data have;
infile cards expandtabs truncover;
input Code	ClientName $	Balance;
cards;2	ABC	10
2	ABC	20
2	ABC	30
3       PQR     40
4       XYZ     50;
run

Then Output must give

Code    Client  Balance  E1                          E2
2	ABC	10       10/(10+20+30+40+50)        150
2	ABC	20       20/(10+20+30+40+50)        150
2	ABC	30       30/(10+20+30+40+50)        150
3       PQR     40       40/(10+20+30+40+50)        150
4       XYZ     50       50/(10+20+30+40+50)        150

I cannot group by since SUM is required for whole column.

Thanks In Advance

Re: SUM of column

DJongman — Fri, 22 Jul 2016 12:47:15 GMT

Did you try to remove the

group by Code,ClientName

code?

Re: SUM of column

yudhishtirb — Fri, 22 Jul 2016 12:54:11 GMT

Thanks, its working 🙂