Exploring, modeling, predicting and reporting with SAS Visual Analytics and SAS Visual Statistics

Flattening a Hierarchy in SAS VA

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Flattening a Hierarchy in SAS VA

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


Accepted Solutions
Solution
‎07-14-2017 05:20 AM
Frequent Contributor
Posts: 120

Re: Flattening a Hierarchy in SAS VA

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

View solution in original post


All Replies
Contributor
Posts: 22

Re: Flattening a Hierarchy in SAS VA

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. 

Occasional Contributor
Posts: 5

Re: Flattening a Hierarchy in SAS VA

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.

Frequent Contributor
Posts: 120

Re: Flattening a Hierarchy in SAS VA

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

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;

Occasional Contributor
Posts: 5

Re: Flattening a Hierarchy in SAS VA

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

Solution
‎07-14-2017 05:20 AM
Frequent Contributor
Posts: 120

Re: Flattening a Hierarchy in SAS VA

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

Occasional Contributor
Posts: 5

Re: Flattening a Hierarchy in SAS VA

Thanks Fredrike!!

☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 299 views
  • 3 likes
  • 3 in conversation