Hi,
I'm looking for help in the following scenario,
I have two tables, one with project details like project id, employee_name and employe_role and the other with employee name and manager, mock data is below.
111 | AA | Emp_role1 |
111 | BB | Emp_role2 |
111 | CC | Emp_role3 |
111 | DD | Emp_role4 |
222 | DDD | Emp_role3 |
222 | EEE | Emp_role4 |
333 | AA | Emp_role5 |
AA | Manager1 |
BB | Manager1 |
CC | Manager1 |
DD | Manager1 |
EE | Manager5 |
FF | Manager5 |
EEE | Manager2 |
FFF | Manager2 |
I wanted to compare and check if all the employees under the same project id have same manager.
Additional Notes: Each employee can hold different roles in different projects but employee_name and employee_role pair is unique for each project. For example, one person named Chris cannot be a manager and a developer, hence only those employee names in the unique employee, role pairs of each project will be used to compare their managers against.
Let me know if you need further details.
Regards,
AC
Data /proc step method:
Sort both datasets by employee name, and merge them. After that, do a proc sort nodupkey of the resulting table by project id and manager. Now you can run a proc freq or summary by project id to see if a project gets a count > 1.
SQL method:
proc sql;
create table endresult as
select project_id, manager, count(*) as manag_count
from (
select distinct a.project_id, b.manager
from proj a left join manag b
on a.emp_name = b.emp_name
)
group by project_id;
quit;
Note that in your example, DDD does not have a manager.
Not sure how the data relates. Only actual duplication is in AA, but that appears in different project. Therefore nothing fulfills your criteria. Show what your output should look like, maybe that will make it clearer.
Thank you for the reply. The output should say the "project_id say 111 have all the employees working under same manager". If you see the data. Employee AA, BB, CC and DD of project table, under the project 111 have the same manager, manager1 in the table 2. Hope this helps.
PS example data in usable form:
data proj;
infile cards dlm='09'x;
input project_id emp_name $ role :$10.;
cards;
111 AA Emp_role1
111 BB Emp_role2
111 CC Emp_role3
111 DD Emp_role4
222 DDD Emp_role3
222 EEE Emp_role4
333 AA Emp_role5
;
run;
data manag;
infile cards dlm='09'x;
input emp_name $ manager $;
cards;
AA Manager1
BB Manager1
CC Manager1
DD Manager1
EE Manager5
FF Manager5
EEE Manager2
FFF Manager2
;
run;
Data /proc step method:
Sort both datasets by employee name, and merge them. After that, do a proc sort nodupkey of the resulting table by project id and manager. Now you can run a proc freq or summary by project id to see if a project gets a count > 1.
SQL method:
proc sql;
create table endresult as
select project_id, manager, count(*) as manag_count
from (
select distinct a.project_id, b.manager
from proj a left join manag b
on a.emp_name = b.emp_name
)
group by project_id;
quit;
Note that in your example, DDD does not have a manager.
Thank you, appreciate the help!
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.