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
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;
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;
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
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;
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
Did you try to remove the
group by Code,ClientName
code?
Hi
Thanks, its working 🙂
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.