Question vlookup equivalent in SAS

[Geeman]

Hey SAS,

I have 3 variable;

Id  =  which is character has leading zeros ie: 0005

Name = character

Reports_to = character has leading zeros ie: 0008

My sas dataset looks like this:

Id	Name	Reports_to_id	Reports_to_name
000006	Smith, John	000007	Phil, Elias
000007	Phil, Elias	000010	Santa, Clause
000008	Turner, Sandra	200000	Andrew, Skype
000010	Santa, Clause	200000	Andrew, Skype
200000	Andrew, Skype	000006	Smith, John
520001	Walker, Walker	000006	Smith, John
		000006

In excel, I use a Vlookp to populate Reports_To_Name; it bassically shows the persons supervisor that he/she reports to bassed ont he reports_to_id; how would I go about doing that in SAS.....Any suggestions..

Thanks,

[Hima]

Import the excel sheet to convert it into sas data set and then join with ID's.

[Geeman]

Thank you Hima,

The dataset is already in a SAS data set, Id like to create the reports_to_name from Name and corresponding ID.

Thank you,

[ballardw]

One approach would be to use your SAS data set to create a custom format and then assign to the ID as needed.

First step would be to get an ID and Name only version of the data with only one record per Id.

Create a data set from that information to make a CNTLIN dataset for use with proc format with the ID renamed to START', the Name renamed to LABEL, add FMTNAME with a text value similar to "$ID2NAME";

Then

Proc format cntlin=<thatdatasetname>;run;

IF this data is relatively static then you may want to save the format to a permanent library and add that library to the format search path

options append=(fmtserach=<yourlibrarynamehere>));

[Geeman]

Thank you Ballardw. I'll give that I try...I did somehting similar but then i keept getting  this error "This range is repeated, or values overlap: 1-1." I'll try your method. Stay tuned.

[ballardw]

That error usually means you have the START value, ID in your case, duplicated somewhere. If your data accidentally has two Names associated with the same ID then then that issue needs to be addressed.

Select distinct in Proc SQL may be your friend here.

[Geeman]

Still getting the same error, I have Ids that are the same which I need, since the person is acting position for another person. Now i am not sure how to programe that...I might just use the vloockup in Excel then import the file...I would have liked to automate that process.

[Hima]

Can you clarify if this how your data is?

Data set 1

200000 Andrew, Skype 000006

520001 Walker, Walker 000006

200000 Andrew, Skype 000006

520001 Walker, Walker 000006

000006 Smith, John 000007

000007 Phil, Elias 000010

000008 Turner, Sandra 200000

000010 Santa, Clause 200000

Data set 2

000006 Smith, John

000010 Santa, Clause

000007 Phil, Elias

200000 Andrew, Skype

Output

200000 Andrew, Skype 000006 Smith, John

520001 Walker, Walker 000006 Smith, John

200000 Andrew, Skype 000006 Smith, John

520001 Walker, Walker 000006 Smith, John

000006 Smith, John 000007 Phil, Elias

000007 Phil, Elias 000010 Santa, Clause

000008 Turner, Sandra 200000 Andrew, Skype

000010 Santa, Clause 200000 Andrew, Skype

[stat_sas]

proc sql;

select a.*,b.name as Reports_to_name from

have a inner join have b

on a.Reports_to_id=b.id;

quit;

[Geeman]

thanks so much! it worked!

[Tom · Accepted solution]

Just join the table with the variable that needs "looking up" with the table that has the decoded variable that you want to look up.

In this case you seem to be using the same table for both.

data have ;

  infile cards dsd dlm='|' truncover;

  length id reports_to_id $8 name expected $20;

  input id name reports_to_id expected ;

cards;

000006|Smith, John|000007|Phil, Elias

000007|Phil, Elias|000010|Santa, Clause

000008|Turner, Sandra|200000|Andrew, Skype

000010|Santa, Clause|200000|Andrew, Skype

200000|Andrew, Skype|000006|Smith, John

520001|Walker, Walker|000006|Smith, John

run;

proc sql ;

  create table want as

    select a.*,b.name as reports_to_name

    from have a left join have b

    on a.reports_to_id = b.id

    order by a.id

  ;

quit;

[Geeman]

Thanks Tom! I'll give that a try ...finger crossed!!

[Geeman]

Why would I be getting more oberservations in a left join then in a inner join?

[Ksharp]

inner join is actually Cartesian Product .Left join include all of the obs from left dataset , suggest using Left join .

BTW, another way is using Hash Table.

Xia Keshan

[nitram29]

Could anyone walk me through the proc sql in this solution?