topic Re: programming help in SAS Programming

programming help

NewbieSAS23 — Thu, 30 Nov 2017 15:14:10 GMT

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,

Re: programming help

RW9 — Thu, 30 Nov 2017 15:31:17 GMT

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.

Re: programming help

NewbieSAS23 — Thu, 30 Nov 2017 15:37:45 GMT

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.

Re: programming help

Kurt_Bremser — Thu, 30 Nov 2017 15:37:47 GMT

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.

Re: programming help

Kurt_Bremser — Thu, 30 Nov 2017 15:42:43 GMT

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;

Re: programming help

NewbieSAS23 — Thu, 30 Nov 2017 16:02:49 GMT

Thank you, appreciate the help!