topic Re: Org chart, hash join? in SAS Programming

Org chart, hash join?

Putt_Ka — Tue, 07 Oct 2014 16:35:17 GMT

Greetings again all, I'm sure someone has come across this in the past, but I need to create an org chart, and I'm new to the idea of hash joins. I basically want to create a column based on another column so it looks like this:

LastName	ID	Manager	ManagerID
Smith	123	Roberts	456
Roberts	456	-	-
Williams	222	Roberts	456
Daniels	777	Williams	222
Cook	383	Williams	222

I need to create the "Manager" column based on the "manager ID" column (EG look up the manager name for the specific ID). On top of this this list will need to be updates, with other data coming in eventually.

Is creating a hash the best way to do this? I've looked a ton online for answers, and I'm confused as to whether a simple Merge "By" statement would work here, or if I should self(inner) join, or hash would work better.

Any help in pointing me in the right direction would be hugely appreciated!

Re: Org chart, hash join?

ballardw — Tue, 07 Oct 2014 17:00:16 GMT

If you have the ManagerId then maybe:

proc sql;

create table want as

select a.*, b.LastName as Manager

from have as a left join have as b on

a.managerid=b.id;

quit;

Re: Org chart, hash join?

Putt_Ka — Tue, 07 Oct 2014 17:34:24 GMT

HI Ballard, yes I've thought of that, but in some instances all I have is the managerid, and will need their name. I should modify the example above, but tough to do as I'm on my phone now.