topic Re: PeopleSoft Hierarchy Roll Up Reports Data Management in SAS Data Management

PeopleSoft Hierarchy Roll Up Reports Data Management

simkinm2 — Fri, 15 Jun 2018 14:19:46 GMT

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

Re: PeopleSoft Hierarchy Roll Up Reports Data Management

ballardw — Fri, 15 Jun 2018 14:42:11 GMT

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?

Re: PeopleSoft Hierarchy Roll Up Reports Data Management

TomKari — Fri, 15 Jun 2018 15:13:52 GMT

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

Re: PeopleSoft Hierarchy Roll Up Reports Data Management

simkinm2 — Fri, 15 Jun 2018 16:33:32 GMT

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).

Re: PeopleSoft Hierarchy Roll Up Reports Data Management

TomKari — Fri, 15 Jun 2018 20:28:33 GMT

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;

Re: PeopleSoft Hierarchy Roll Up Reports Data Management

simkinm2 — Fri, 22 Jun 2018 13:48:29 GMT

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!