Solved
Contributor
Posts: 25

# SUM of column

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;

Accepted Solutions
Solution
‎07-22-2016 08:53 AM
Occasional Contributor
Posts: 11

## Re: SUM of column

Did you try to remove the

``group by Code,ClientName``

code?

All Replies
Super User
Posts: 9,606

## Re: SUM of column

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;```
Super User
Posts: 10,787

## Re: SUM of column

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

```
Contributor
Posts: 25

## Re: SUM of column

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             E22	ABC	10       10/(10+20+30)  60
2	ABC	20       20/(10+20+30)  60
2	ABC	30       30/(10+20+30)  60Can we do like this ?Thanks In Advance```

Occasional Contributor
Posts: 11

## Re: SUM of column

[ Edited ]
``````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;``````
Contributor
Posts: 25

## Re: SUM of column

[ Edited ]

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	303       PQR     404       XYZ     50;
run``````

Then Output must give

```Code    Client  Balance  E1                          E22	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)        1503       PQR     40       40/(10+20+30+40+50)        1504       XYZ     50       50/(10+20+30+40+50)        150```

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

Solution
‎07-22-2016 08:53 AM
Occasional Contributor
Posts: 11

## Re: SUM of column

Did you try to remove the

``group by Code,ClientName``

code?

Contributor
Posts: 25

## Re: SUM of column

Hi

Thanks, its working

☑ This topic is solved.