I'd probably go for a more traditional way for creating an employee dimension table.
The following code works under the assumption that each employee has only 1 direct line manager. You should also add the director to your initial data.
data WORK.HAVE;
infile datalines dsd truncover;
input Employee_ID:BEST. Employee_Position:$23. Manager_ID:BEST. Manager_Position:$23.;
datalines4;
11111,Sr Analyst Revenue Mgmt,66666,Sr Mgr Revenue Mgmt
22222,General Clerk,11111,Sr Analyst Revenue Mgmt
33333,Analyst Revenue Mgmt,133332,Mgr Revenue Mgmt
44444,Asst Mgr Revenue Mgmt,133332,Mgr Revenue Mgmt
66666,Sr Mgr Revenue Mgmt,55555,Dir Accounting
77777,Sr Analyst Revenue Mgmt,133332,Mgr Revenue Mgmt
88888,General Clerk,11111,Sr Analyst Revenue Mgmt
99999,Sr Analyst Revenue Mgmt,66666,Sr Mgr Revenue Mgmt
111110,Sr Analyst Revenue Mgmt,133332,Mgr Revenue Mgmt
122221,Sr Analyst Revenue Mgmt,133332,Mgr Revenue Mgmt
133332,Mgr Revenue Mgmt,66666,Sr Mgr Revenue Mgmt
144443,General Clerk,11111,Sr Analyst Revenue Mgmt
;;;;
run;
/* work out hierarchy structure and number of levels */
data hierarchy;
if _n_=1 then
do;
if 0 then set have(keep=Employee_ID Manager_ID);
dcl hash mgr(dataset:'have(keep=Employee_ID Manager_ID)');
mgr.defineKey('Employee_ID');
mgr.defineData('Manager_ID');
mgr.defineDone();
dcl hash emp(dataset:'have(keep=Employee_ID Manager_ID)');
emp.defineKey('Manager_ID');
emp.defineData('Employee_ID');
emp.defineDone();
end;
set have(keep=Employee_ID Manager_ID);
/* only create chain for leaf employees (=no direct reports) */
if emp.check(key:Employee_ID)=0 then return;
level=1;
output;
do while( mgr.find(key:Manager_ID) = 0 );
level+1;
output;
end;
run;
/* determine hierarchy level per manager and max. hierarchy levels */
proc sql noprint;
create table hier as
select
manager_id,
max(level) as level
from hierarchy
group by manager_id
;
select cats(max(level)) into :n_hier from hier;
quit;
/* build employee dimension table */
data emp_dim(drop=Manager_ID Manager_Position level);
if _n_=1 then
do;
if 0 then set have hier;
dcl hash hier(dataset:'hier');
hier.defineKey('manager_id');
hier.defineData('level');
hier.defineDone();
dcl hash mgr(dataset:'have(keep=Employee_ID Manager_ID)');
mgr.defineKey('Employee_ID');
mgr.defineData('Manager_ID');
mgr.defineDone();
end;
call missing(of _all_);
set have;
array hiers {&n_hier} 8 level&n_hier - level1;
level0='Company';
if hier.find()=0 then hiers[level]=Manager_ID;
do while( mgr.find(key:Manager_ID) = 0 );
if hier.find()=0 then hiers[level]=Manager_ID;
end;
run;
Result:
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.
-
4413 views
-
2 likes
-
-
