Question vlookup equivalent in SAS

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

Question vlookup equivalent in SAS

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:

IdNameReports_to_idReports_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,


Accepted Solutions
Solution
‎07-11-2016 10:52 PM
Super User
Super User
Posts: 6,364

Re: Question vlookup equivalent in SAS

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;

View solution in original post


All Replies
Regular Contributor
Posts: 233

Re: Question vlookup equivalent in SAS

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

Occasional Contributor
Posts: 13

Re: Question vlookup equivalent in SAS

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,

Grand Advisor
Posts: 10,223

Re: Question vlookup equivalent in SAS

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>));

Occasional Contributor
Posts: 13

Re: Question vlookup equivalent in SAS

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. Smiley Happy

Grand Advisor
Posts: 10,223

Re: Question vlookup equivalent in SAS

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.

Occasional Contributor
Posts: 13

Re: Question vlookup equivalent in SAS

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.

Regular Contributor
Posts: 233

Re: Question vlookup equivalent in SAS

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

Trusted Advisor
Posts: 1,203

Re: Question vlookup equivalent in 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;

Occasional Contributor
Posts: 13

Re: Question vlookup equivalent in SAS

thanks so much! it worked!

Solution
‎07-11-2016 10:52 PM
Super User
Super User
Posts: 6,364

Re: Question vlookup equivalent in SAS

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;

Occasional Contributor
Posts: 13

Re: Question vlookup equivalent in SAS

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

Occasional Contributor
Posts: 13

Re: Question vlookup equivalent in SAS

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

Grand Advisor
Posts: 9,584

Re: Question vlookup equivalent in SAS

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

Senior User
Posts: 1

Re: Question vlookup equivalent in SAS

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

☑ This topic is SOLVED.

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

Discussion stats
  • 15 replies
  • 4644 views
  • 4 likes
  • 8 in conversation