PeopleSoft Hierarchy Roll Up Reports Data Management

[simkinm2]

I have some HR data from our PeopleSoft System for which I am trying to create reports for employees of a certain job type.  I've provided an example of the data below. 

Essentially, I need to create some reports for anyone that does NOT have the OFFICE_CD of "individual."  Furthermore, I need to create rolled-up reports at varying levels in our HR hierarchy.  For example, an individual reports to a supervisor, and the supervisor reports to a manager, and a manager reports to a director, and a director reports to a VP, and so on.  The data provides the employee's ID # (EMPLID) and their direct supervisor only.  I'm struggling with how to link data in way to provide reports such as a director-level's report roll-up, which needs to include all the managers, supervisors, and individuals that report underneath them.  The data is very linear, but I'm hitting a roadblock so any suggestions would be greatly appreciated!

Thanks!

COMPANY          EMPLID                                 NAME                                   OFFICER_CD                       SUPERVISOR_EMPLID

A                             0931616061                         Ale,Ginger                          Individual                            0389203585101

B                             0415656062                         Daniels,Jack                        Individual                            0389203585101

C                             0201590112                         O'Hara,Scarlet                   Individual                            0389203585101

D                             0704929134                         Palmer,Arnold                   Individual                            0389203585101

A                             0307800788                         Peach,Georgia                  Individual                            0389203585101

A                             0912890678                         Sour,Brandy                       Individual                            0389203585101

B                             0036176062                         Walker,Johnny                  Individual                            0389203585101

B                             0389203585101                  Temple,Shirley                  Supervisor                          0131284859101

B                             0131284859101                   Rogers,Ginger                   Manager I                           0048586929101

A                             0048586929101                    Tia,Mai                                 Dir I                                      0569039562101

B                             0569039562101                     Von Teese,Dita                 VP I                                        0044980664101

C                             0044980664101                   Henkel,Margarita             Exec Ldr                               0453958372101

A                             0453958372                        Beam,James                      Exec Ldr                               0430450225801

A                             0430450225801                    Collins,Thomas                  CEO       

[ballardw]

It helps to provide example data in the form of a data step if you want example code that works.

Also it clears up issues such as is the name of the variable OFFICE_CD, as used in the problem description, or OFFICER_CD as shown in the example data.

You will have to provide your hierarchy in terms of the values of your variables. Otherwise folks have to guess about things like possible existence of "Manager 2" since you show a "Manager I" and which would be "higher" rank.

And what do your reports have to show? Counts of subordinates? List of subordinates? An example of one or more of the reports might work.

What role does the Company field play in your desired report(s) if any?

[TomKari]

I think your data may be a little muddled.

If I understand right, Collins,Thomas (ID 801) is the supervisor of Beam,James (ID 372), but Beam,James isn't the supervisor of anyone, so your cascade is immediately broken.

Tom

[simkinm2]

Here's an example of the data in a datalines example:

DATA HIERARCHY; 
LENGTH EMPLOYEE_TYPE $ 20.;
INPUT COMPANY $ EMPLID EMPLOYEE_TYPE $ DIRECT_SUPERVISORID Score ;
DATALINES;
A 1001 Individual 2001 35
A 1002 Individual 2001 50
A 1003 Individual 2002 66
A 1004 Individual 3001 98
A 1005 Individual 3001 45
B 1006 Individual 2003 67 
B 1007 Individual 2003 34
B 1008 Individual 2004 56 
B 1009 Individual 3002 90
C 1010 Individual 3003 12
C 1011 Individual 3003 36
C 1012 Individual 3004 87
C 1013 Individual 3004 56
A 2001 Supervisor 3001 78
A 2002 Supervisor 3001 99
B 2003 Supervisor 3002 100
B 2004 Supervisor 4001 34
A 3001 Manager 4003 15
B 3002 Manager 4001 56
C 3003 Manager 4002 67
C 3004 Manager 4002 08
B 4001 Director 5002 23
C 4002 Director 5003 50
A 4003 Director 5001 45
A 5001 VP 999 65
B 5002 VP 999 35	
C 5003 VP 999 07 
;
RUN; 

I've got some survey data that provides a percent positive score (Score in above); each person who responds to this survey belongs to company A, B, C, etc. and their survey response includes their employee_type as well as the person they directly report to (Director_Supervisor_ID).  Levels of employee types go from Individual, then Supervisor, then Manager, then Director, then finally to VP (the highest level).  I have to provide reports for each person who is NOT an individual for each company and the scores and sample size for everyone that reports underneath them.  For example, within company A, the Manger 3001 would need the average of all the scores for all the supervisors and all the individuals that report under the supervisors, with a final count of all the survey respondents (so a count of all the individuals and supervisors).

[TomKari · Accepted solution]

Here's some code that extends out the supervisor/employee relationship at each level. As I'm not really sure what your reporting needs are in detail, hopefully these tables will give you a start to doing some experimenting and refining things a bit better.

Tom

proc sql noprint;
	create table Inter01
		as select s.COMPANY, s.EMPLID as EmployeeLevel1, s.Score
			from Hierarchy s
				where s.DIRECT_SUPERVISORID = 999;
quit;

proc sql noprint;
	create table Inter02
		as select s.COMPANY, s.EmployeeLevel1,
			e.EMPLID as EmployeeLevel2, e.Score
		from Inter01 s left join Hierarchy e
			on s.EmployeeLevel1 = e.DIRECT_SUPERVISORID;
quit;

proc sql noprint;
	create table Inter03
		as select s.COMPANY, s.EmployeeLevel1, s.EmployeeLevel2,
			e.EMPLID as EmployeeLevel3, e.Score
		from Inter02 s left join Hierarchy e
			on s.EmployeeLevel2 = e.DIRECT_SUPERVISORID;
quit;

proc sql noprint;
	create table Inter04
		as select s.COMPANY, s.EmployeeLevel1, s.EmployeeLevel2, s.EmployeeLevel3,
			e.EMPLID as EmployeeLevel4, e.Score
		from Inter03 s left join Hierarchy e
			on s.EmployeeLevel3 = e.DIRECT_SUPERVISORID;
quit;

proc sql noprint;
	create table Inter05
		as select s.COMPANY, s.EmployeeLevel1, s.EmployeeLevel2, s.EmployeeLevel3, s.EmployeeLevel4,
			e.EMPLID as EmployeeLevel5, e.Score
		from Inter04 s left join Hierarchy e
			on s.EmployeeLevel4 = e.DIRECT_SUPERVISORID;
quit;

[simkinm2]

This is very helpful!  This is very close to what I need and with a few adjustments I think I'll have the final data structure.  Thank you!