topic Re: SAS Query in SAS Programming

SAS Query

scb — Sun, 26 Mar 2017 14:12:08 GMT

I would like to obtain the commission for all sales achieved. Anyone can help? Thanks.

The desired would be:

ID Sales Commission
111 400 400
111 200 200
111 200 200
112 600 600
112 250 250
112 100 100
112 50 50
113 120 280
113 180 420
114 200 400
114 300 600
114 100 200

data Comm;
input ID Commission;
datalines;
111 800
112 1000
113 700
114 1200
;
run;

data sales;
input ID Sales;
datalines;
111 400
111 200
111 200
112 600
112 250
112 100
112 50
113 120
113 180
114 200
114 300
114 100
;
run;

Re: SAS Query

art297 — Sun, 26 Mar 2017 15:12:34 GMT

I think the following will do what you want:

data salesplus;
  do until (last.id);
    set Sales;
    by id;
    if first.id then total=sales;
    else total+sales;
  end;
  do until (last.id);
    set Sales;
    by id;
    cpart=sales/total;
    output;
  end;
run;

data want (drop=total cpart _:);
  merge comm (rename=(commission=_commission)) salesplus;
  by id;
  commission=_commission*cpart;
run;

Art, CEO, AnalystFinder.com

Re: SAS Query

lakshmi_74 — Sun, 26 Mar 2017 16:37:53 GMT

proc sql;
create table new as select id, sum(sales) as tot_sales from sales group by id;
select sales.id,sales,(commission/tot_sales)*sales as final_commission
from sales left join new on sales.id=new.id
left join comm on sales.id=comm.id;
quit;