Lookup in SAS - Picking only first matched value

[sachin01663]

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

[Linlin · Accepted solution]

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

[sachin01663]

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

[Linlin]

Hi Sachin,

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

[anu]

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!

[Reeza]

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;

[srinidhi]

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

[MikeZdeb]

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;

[polingjw]

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;

[sachin01663]

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

Sachin

[polingjw]

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;