topic Split the dept bonus equally to all employees in that dept in SAS Programming

Split the dept bonus equally to all employees in that dept

_el_doredo — Sun, 27 Jun 2021 13:17:57 GMT

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

Re: Split the dept bonus equally to all employees in that dept

PaigeMiller — Sun, 27 Jun 2021 15:37:43 GMT

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;

Re: Split the dept bonus equally to all employees in that dept

_el_doredo — Sun, 27 Jun 2021 15:27:44 GMT

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;

Re: Split the dept bonus equally to all employees in that dept

PaigeMiller — Sun, 27 Jun 2021 15:36:43 GMT

@_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)