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
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;
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;
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;
@_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)
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.