BookmarkSubscribeRSS Feed
scb
Obsidian | Level 7 scb
Obsidian | Level 7

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;

2 REPLIES 2
art297
Opal | Level 21

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

 

lakshmi_74
Quartz | Level 8

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 688 views
  • 0 likes
  • 3 in conversation