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 🙂
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.