topic Lookup in SAS - Picking only first matched value in SAS Procedures

Lookup in SAS - Picking only first matched value

sachin01663 — Thu, 26 Jan 2012 14:06:14 GMT

Hello,

I am little bit fimilar with Merge in SAS and Joins in Proc SQL. May be I am missing something, but I am struggling to do a simple lookup in SAS as we do in vlookup in excel. For example

Dataset A has two variables:

ID Visits

1 Two

2 Three

1 One

3 Two

Dataset B has two variable

ID Location

1 A

2 B

3 A

1 C

2 B

All I want is my source file DatasetA should look in Dataset B, match by ID and give me the first matched Location. Dont want duplicates. Output should be:

ID Visits Location

1 Two A

2 Three B

1 One A (See location is not C but gave me the first macthed Location again)

3 Two A

Hope thats clear.

Thanks

Sac01663

Lookup in SAS - Picking only first matched value

Linlin — Thu, 26 Jan 2012 14:39:07 GMT

How about:

data A;

input ID $ :Visits $;

cards;

1 Two

2 Three

1 One

3 Two

;

data B;

input ID $ :Location $;

cards;

1 A

2 B

3 A

1 C

2 B

;

proc sort data=b nodupkey;

by id ;

run;

proc sql;

create table want as

select a.id,visits,location

from a,b

where a.id=b.id;

quit;

proc print data=want;run;

Obs ID Visits Location

1 1 Two A

2 2 Three B

3 1 One A

4 3 Two A

Linlin

Lookup in SAS - Picking only first matched value

sachin01663 — Thu, 26 Jan 2012 14:47:35 GMT

Thanks Linlin,

There is way out by removing duplicates. However, I am looking for a generic solution for such kind of problems. vlookup has been very useful and solves more than half of your problems.

Basically, does anything replaces vlookup (excel) in SAS? Any procedures, joins?

Regards,

Sachin

Lookup in SAS - Picking only first matched value

Linlin — Thu, 26 Jan 2012 14:53:03 GMT

Hi Sachin,

I am not familar with vlookup. Maybe someone on the Forum would be able to answer your question.

Lookup in SAS - Picking only first matched value

anu — Thu, 26 Jan 2012 15:51:11 GMT

Hi Sachin,

http://www2.sas.com/proceedings/sugi28/250-28.pdf. Check out this paper it talks about sas functions and its equivalent in excel. Hope this helps!

Re: Lookup in SAS - Picking only first matched value

MikeZdeb — Thu, 26 Jan 2012 19:02:43 GMT

hi ... here's another idea ...

data a;

input id visits : $5. @@;

datalines;

1 Two 2 Three 1 One 3 Two

;

data b;

input id location : $1. @@;

datalines;

1 A 2 B 3 A 1 C 2 B

;

proc sort data=b out=b (index=(id));

by id location;

run;

data ab;

set a;

set b key=id / unique;

if _error_ then do; _error_ = 0; call missing(location); end;

run;

Re: Lookup in SAS - Picking only first matched value

Reeza — Thu, 26 Jan 2012 19:36:44 GMT

I think a format would work well for the types of lookup you're doing, but in all cases you'll need to have the lookup table de-duplicated at some point I believe.

Proc format solution to your problem involves

1. De-duplicating the data

2. Creating the format

3. Applying it to your dataset.

data have;

input id visits : $5. @@;

datalines;

1 Two 2 Three 1 One 3 Two

;

data lookup;

input id location : $1. @@;

datalines;

1 A 2 B 3 A 1 C 2 B

;

*Sort variables and rename id and location;

proc sort data=lookup out=l_format (rename=(id=start location=label));

by id location;

run;

data l_format;

set l_format;

*Set type of format and format name;

retain type 'N' fmtname 'lookup';

by start;

if first.start; *Keep only first of each kind;

run;

*create format;

proc format cntlin=l_format;

run;

data ab;

set a;

lookup=put(id, lookup.);

run;

Re: Lookup in SAS - Picking only first matched value

polingjw — Thu, 26 Jan 2012 20:11:26 GMT

I believe that the answer is no, there is not a single procedure that does exactly what vlookup does. However, as demonstrated, there are many ways to get the desired results. Here is yet another way that does not require sorting:

data ab;

if _n_ = 1 then do;

if 0 then set b;

declare hash h(dataset:'b', multidata:'y');

h.definekey('id');

h.definedata('location');

h.definedone();

end;

set a;

if h.find() then call missing(location);

run;

Re: Lookup in SAS - Picking only first matched value

sachin01663 — Fri, 27 Jan 2012 19:31:03 GMT

Thanks people. All the aswers are helpful. There are many ways to get this done.

Sachin

Re: Lookup in SAS - Picking only first matched value

polingjw — Mon, 30 Jan 2012 13:30:01 GMT

If you really like using the Excel vlookup function, you could even create your own user-defined vlookup function with PROC FCMP. However, you should note that of all the solutions posed, my guess is that this one will be the least efficient by far.

%macro vlookup;

%let lookup_value = %sysfunc(dequote(&lookup_value));

%let lookup_table = %sysfunc(dequote(&lookup_table));

%let lookup_column = %sysfunc(dequote(&lookup_column));

%let return_column = %sysfunc(dequote(&return_column));

%let results = ;

data _null_;

if _n_=1 then set &lookup_table(keep=&lookup_column);

type = vtype(&lookup_column);

call symputx("type", type);

stop;

run;

data _null_;

set &lookup_table(obs = 1 keep=&lookup_column &return_column);

%if &type = N %then where &lookup_column = &lookup_value;;

%if &type = C %then where &lookup_column = "&lookup_value";;

call symputx("results", &return_column);

run;

%mend;

proc fcmp outlib=sasuser.funcs.general;

function vlookup(lookup_value $, lookup_table $, lookup_column $, return_column $) $;

length results $ 200;

rc=run_macro('vlookup', lookup_value, lookup_table, lookup_column, return_column, results);

return(results);

endsub;

quit;

data a;

input id visits : $5. @@;

datalines;

1 Two 2 Three 1 One 3 Two

;

data b;

input id location : $1. @@;

datalines;

1 A 2 B 3 A 1 C 2 B

;

run;

options cmplib=sasuser.funcs;

data ab;

set a;

location=vlookup(id, 'b', 'id', 'location');

run;

Re: Lookup in SAS - Picking only first matched value

srinidhi — Thu, 27 Oct 2016 13:18:21 GMT

it was nice answer and even i'm also faced same problem, thanks for your super answer.