Flattening a Hierarchy in SAS VA

[KarunaT]

Hi, 

I have Employee data in which Employee id and Manager id are present.

An employee can have many levels of Managers for eg. his Manager, his Manager's Manager and so on.

Is there any way by which when I click on Employee, I get the data like

EMP -> his Manager -> his Manager's Manager -> upto the level specified in SAS VA.

Thanks in advance!!

Karuna

[ScottJP]

Hi - You can surely just create a new Hieracrchy. As long as you've got these fields in your data, you can create the hierarchy. Then, clicking on an employee in, for example, a chart object, will allow a report user to drill down to the levels you've put in the hierarchy. 

I have done the same sort of thing for Sales Region --> Sales Manager --> Sales Area --> Sales Representative. 

[KarunaT]

Thanks for the prompt reply, Scott.

But I think i was not able to explain the problem in hand.

I too have created a Geo Hierarchy with Country, State and City.

But currently, I have two fields Emp Id and Mngr Id, for 1st level, we will find the Mngr Id for the Employee; for the 2nd level, the Mngr id will become Emp Id and we will find the Mngr Id till we get to the highest Level.

Hope I am clear this time.

[FredrikE]

I don't think you can achieve that in VA, I tried some different ways  but did not get what I think you want 🙂

I have done this a couple of times and in all of them I have flattened the hierarchy in base-code before I loaded it into VA.

Like:

data test;

length uid mid 8 uname mname $32;

input uid mid uname;

datalines;

1 10 usr1

2 10 usr2

3 20 usr3

4 20 usr4

5 30 usr5

6 40 usr6

7 50 usr7

10 100 mgr10

20 100 mgr20

30 200 mgr30

40 300 mgr40

50 . mgr50

100 1000 mgr100

200 1000 mgr200

300 . mgr300

1000 . mgr1000

;

run;

proc sql;

create table hier as

select a.uname, b.uname as mgrname_lev1, c.uname as mgrname_lev2, d.uname as mgrname_lev3 from test a

left join test b

on a.mid = b.uid

left join test c

on b.mid = c.uid

left join test d

on c.mid = d.uid

order by 1,2,3,4

;

quit;

[KarunaT]

Hi Fredrike,

Thanks for your Reply. This is exactly what I was looking for.

But in my case the level of Managers is unknown. An employee can have two levels of Managers above him while other might have six levels of Managers.

Is there any way, that can be achieved.

Thanks,

Karuna

[FredrikE · Accepted solution]

I usually descide the number of levels possible, let say 10 and then just adds more left joins to my example.

Next step is to descide what to do with the "branches" that does not go all the way to 10.

One way is to let them be empty and another is to inherit the previous value all the way up to level 10.

All of this depends on how you want to use the hierarchy.

BR

Fredrik

[KarunaT]

Thanks Fredrike!!