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;
... View more
