I am using a proc means to calculate the share of the payments made by business line, the data looks like this:
data Test;
input ID Business_Line Payment2017;
Datalines;
1 1 1000
2 1 2000
3 1 3000
4 1 4000
5 2 500
6 2 1500
7 2 3000
;
run;
i'm looking to calculate an additional column which, by group (business_line) calculates the percentage share (weight) of the payment as such:
data Test;
input ID Business_Line Payment2017 share;
Datalines;
1 1 1000 0.1
2 1 2000 0.2
3 1 3000 0.3
4 1 4000 0.4
5 2 500 0.1
6 2 1500 0.3
7 2 3000 0.6
;
run;
the code I have used so far:
proc means data = test noprint;
class ID;
by business_line;
var Payment2017;
output out=test2
sum = share;
weight = payment2017/share;
run;
I have also tried
proc means data = test noprint;
class ID;
by business_line;
var Payment2017 /weight = payment2017;
output out=test3 ;
run;
appreciate the help.
Do it in SQL:
proc sql;
create table want as
select a.*, payment2017 / sum(payment2017) as share
from test a
group by business_line
order by id
;
quit;
Do it in SQL:
proc sql;
create table want as
select a.*, payment2017 / sum(payment2017) as share
from test a
group by business_line
order by id
;
quit;
I always forget about SQL
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.