topic Re: Question vlookup equivalent in SAS in SAS Programming

Question vlookup equivalent in SAS

Geeman — Tue, 16 Sep 2014 14:20:48 GMT

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,

Re: Question vlookup equivalent in SAS

Hima — Tue, 16 Sep 2014 14:28:56 GMT

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

Re: Question vlookup equivalent in SAS

Geeman — Tue, 16 Sep 2014 14:34:21 GMT

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,

Re: Question vlookup equivalent in SAS

ballardw — Tue, 16 Sep 2014 14:47:00 GMT

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

Re: Question vlookup equivalent in SAS

Geeman — Tue, 16 Sep 2014 14:52:08 GMT

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.

Re: Question vlookup equivalent in SAS

Hima — Tue, 16 Sep 2014 14:53:51 GMT

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

Re: Question vlookup equivalent in SAS

stat_sas — Tue, 16 Sep 2014 15:22:33 GMT

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;

Re: Question vlookup equivalent in SAS

ballardw — Tue, 16 Sep 2014 15:32:53 GMT

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.

Re: Question vlookup equivalent in SAS

Geeman — Tue, 16 Sep 2014 16:08:08 GMT

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.

Re: Question vlookup equivalent in SAS

Tom — Wed, 17 Sep 2014 05:46:50 GMT

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;

Re: Question vlookup equivalent in SAS

Geeman — Wed, 17 Sep 2014 12:27:50 GMT

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

Re: Question vlookup equivalent in SAS

Geeman — Wed, 17 Sep 2014 12:55:53 GMT

thanks so much! it worked!

Re: Question vlookup equivalent in SAS

Geeman — Wed, 17 Sep 2014 17:54:45 GMT

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

Re: Question vlookup equivalent in SAS

Ksharp — Thu, 18 Sep 2014 12:28:04 GMT

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

Re: Question vlookup equivalent in SAS

FriedEgg — Thu, 18 Sep 2014 14:53:06 GMT

I would never recommend actually using this approach as it is far overly complicated, fairly inflexible and the worst performing of all the suggestions:

proc fcmp outlib=work.func.excl;

function vlookup(dsname $, idvar $, id, col_index) $ 200;

length val $ 200;

dsn=cats(dsname, '(where=(', idvar, '=', id, '))');

dsid=open(dsn);

if dsid=0 then do;

put '4552524f523a20'x 'No lookup value for ' id=;

return ('');

end;

vartype=vartype(dsid, col_index);

rc=fetch(dsid);

if rc ne 0 then do;

msg=sysmsg();

put msg;

return ('');

end;

if vartype='C' then do;

val=getvarc(dsid, col_index);

end;

else do;

varfmt=varfmt(dsid, col_index);

val=putn(getvarn(dsid, col_index), varfmt);

end;

rc=close(dsid);

return (val);

endsub;

run;

options cmplib=work.func;

data have;

input id name $14. report_to;

cards;

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

set have;

report_to_name=vlookup('have', 'id', report_to, 2);

run;

obs	id	name	report_to	reports_to_name
1	200000	Andrew, Skype	6	Smith, John
2	520001	Walker, Walker	6	Smith, John
3	200000	Andrew, Skype	6	Smith, John
4	520001	Walker, Walker	6	Smith, John
5	6	Smith, John	7	Phil, Elias
6	7	Phil, Elias	10	Santa, Clause
7	8	Turner, Sandra	200000	Andrew, Skype
8	10	Santa, Clause	200000	Andrew, Skype

Re: Question vlookup equivalent in SAS

nitram29 — Wed, 22 Feb 2017 17:36:02 GMT

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