DATA Step, Macro, Functions and more

programming help

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

programming help

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


Accepted Solutions
Solution
‎11-30-2017 11:02 AM
Super User
Posts: 10,530

Re: programming help

Posted in reply to NewbieSAS23

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Super User
Super User
Posts: 9,802

Re: programming help

Posted in reply to NewbieSAS23

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.

New Contributor
Posts: 3

Re: programming help

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.

Super User
Posts: 10,530

Re: programming help

Posted in reply to NewbieSAS23

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Solution
‎11-30-2017 11:02 AM
Super User
Posts: 10,530

Re: programming help

Posted in reply to NewbieSAS23

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
New Contributor
Posts: 3

Re: programming help

Posted in reply to KurtBremser

Thank you, appreciate the help!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 115 views
  • 0 likes
  • 3 in conversation