CODEWITHSUNDEEP
excelsas
Sara E
Sara E

Reputation: 335

SAS Code that works like Excel's "VLOOKUP" function

I'm looking for a SAS Code that works just like "VLOOKUP" function in Excel.

I have two tables: table_1 has an ID column with some other columns in it with 10 rows. Table_2 has two columns: ID and Definition with 50 rows. I want to define a new variable "Definition " in table_1 and lookup the ID values from table_2.

I haven't really tried anything other than merge. but merge keeps all the extra 40 variables from table_2 and that's not what I like.

Thanks, SE

Upvotes: 1

Views: 19312

Answers (2)

Joe
Joe

Reputation: 63434

Here is one very useful (and often very fast) method specifically for 1:1 matching, which is what VLOOKUP does. You create a Format or Informat with the match-variable and the lookup-result, and put or input the match-variable in the master table.

data class_income;
set sashelp.class(keep=name);
income =  ceil(12*ranuni(7));
run;


data for_format;
set class_income end=eof;
retain fmtname 'INCOMEI';
start=name;
label=income;
type='i'; *i=informat numeric, j=informat character, n=format numeric, c=format character;
output;
if eof then do;
 hlo='o'; *hlo contains some flags, o means OTHER for nonmatching records;
 start=' ';
 label=.;
 output;
end;
run;

proc format cntlin=for_format;
quit;

data class;
set sashelp.class;
income = input(name,INCOMEI.);
run;

Upvotes: 1

Hong Ooi
Hong Ooi

Reputation: 57696

The simplest way is to use the keep option on your merge statement.

data result;
    merge table_1 (in=a) table_2 (in=b keep=id definition);
    by id;
    if a;
 run;

An alternative that means you don't have to sort your datasets is to use proc sql.

proc sql;
    create table result as
    select a.*,
           b.definition
    from table_1 a
    left join table_2 b on a.id = b.id;
quit;

Finally, there is the hash table option if table_2 is small:

data result;
    if _n_ = 1 then do;
        declare hash b(dataset:'table_2');
        b.definekey('id');
        b.definedata('definition');
        b.definedone();
        call missing(definition);
    end;
    set table_1;
    b.find();
run;

Upvotes: 4

Related Questions