Split the dept bonus equally to all employees in that dept

[_el_doredo]

Hi Experts,

I am learning on SAS. I came across this query. I am not able to find answer for this one. I hope you guys will help me on this with the method.

data dept_table;
input dept_name $ total_bonus ;
cards;
IT 50000
HR 69000
GL 90000
;
run;

Data Emp_table;
input dept_name $ emp_id;
cards;
IT 1
IT 2
IT 3
IT 4
IT 5
HR 23
HR 45
HR 86
GL 96
GL 52
GL 25
;
run;

I need to split the total dept bonus to all the employees in that specific dept. I tried but doing so my subquery resulting multiple rows so not getting exact result...

My expected result is

This is the program i tried:

proc sort data=dept_table;
by dept_name;
run;

proc sort data=emp_table;
by dept_name;
run;

data final;
merge dept_table(In=A) emp_table(IN=B);
by dept_name;
proc print;
run;

proc sql;
select dept_name,emp_id,(select count(emp_id) from final group by dept_name)
from final;
quit;

I hope you guys can help me on this

[PaigeMiller · Accepted solution]

This creates the table you want. I didn't do the division of the total bonus divided by the number of employees, I left that out and assign it to you as a homework problem.

data dept_table;
	input dept_name $ total_bonus;
	cards;
IT 50000
HR 69000
GL 90000
;
run;

Data Emp_table;
	input dept_name $ emp_id;
	cards;
IT 1
IT 2
IT 3
IT 4
IT 5
HR 23
HR 45
HR 86
GL 96
GL 52
GL 25
;
run;

proc sql;
	create table want as 
        select a.dept_name, a.emp_id, count(a.emp_id) as count, b.total_bonus from 
		emp_table a left join dept_table b on a.dept_name=b.dept_name 
        group by a.dept_name;
quit;

[_el_doredo]

Thank you so much... I tried with the sub query..

proc sql;
select a.dept_name, a.emp_id, count(emp_id) as count, b.total_bonus,
(b.total_bonus/ calculated count) as bonus from
emp_table a left join dept_table b on a.dept_name=b.dept_name group by
a.dept_name;
quit;

[PaigeMiller]

---

@_el_doredo wrote:

Thank you so much... I tried with the sub query..

proc sql;
select a.dept_name, a.emp_id, count(emp_id) as count, b.total_bonus,
(b.total_bonus/ calculated count) as bonus from
emp_table a left join dept_table b on a.dept_name=b.dept_name group by
a.dept_name;
quit;

---

In the future, please help your self (and help us as well) by indenting your code to improve readability (as I have done in my earlier reply)