Proc means - Calculating the share / weight

Question

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.

score 3 · Accepted Answer · answered Mar 29 '18 at 14:17

Proc FREQ will compute percentages. You can divide the PERCENT column of the output to get the fraction, or work with percents downstream.

In this example id crosses payment2017 in order to ensure all original rows are part of the output. If the id was not present, and there were any replicate payment amounts, FREQ would aggregate the payment amounts.

proc freq data=have noprint;
 by business_line;
 table id*payment2017 / out=want all;
 weight payment2017 ;
run;

score 1 · Answer 2 · answered Mar 29 '18 at 13:35

It is convenient to do with proc sql:

proc sql;
   select *, payment2017/sum(payment2017) as share from test group by business_line;
quit;

data step:

data have;
    do until (last.business_line);
    set test;
    by business_line notsorted;
    total+payment2017;
    end;
    do until (last.business_line);
    set test;
    by business_line notsorted;
    share=payment2017/total;
    output;
    end;
    call missing(total);
    drop total;
run;

Proc means - Calculating the share / weight

2 Answers2