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!
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;
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.