BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
NewbieSAS23
Calcite | Level 5

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.

111AAEmp_role1
111BBEmp_role2
111CCEmp_role3
111DDEmp_role4
222DDDEmp_role3
222EEEEmp_role4
333AAEmp_role5

 

AAManager1
BBManager1
CCManager1
DDManager1
EEManager5
FFManager5
EEEManager2
FFFManager2

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

NewbieSAS23
Calcite | Level 5

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.

Kurt_Bremser
Super User

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;
Kurt_Bremser
Super User

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.

NewbieSAS23
Calcite | Level 5

Thank you, appreciate the help!

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
  • 5 replies
  • 675 views
  • 0 likes
  • 3 in conversation