SAS Vlookup in the same table

Question

my case is finding value in this same table (for Variable2 from Value). Can you help me? I need a SAS code for this case.

I tried to solve this way:

data example2;                                                 
 input Variable Value Variable2;                                                  
    datalines;                                                    
    V1 3 V2 
    V2 6 V1 
    V3 4 V5 
    V4 1 V1 
    V5 5 V2 
    ;

    proc sort data=example2;
        by Variable;
    run;
    data example19;
      set example2;
      merge example2 example2 (keep=Value Variable2 rename=(Value=new));                             
    run;  

Answer 1

below code should work for your scenario. I have checked for couple of edge case scenarios and it works as expected. Just check once again if it fails any edge case other this

 data have;
  input variable $ value variable2 $;
  datalines;
  V1 3 V2
  V2 6 V1
  V3 4 V5
  V4 1 V1
  V5 5 V2
 ;


   proc sql;
  create table want as 
     select  a.variable ,a.value, a.variable2 ,   b.value as value2            
  from have a
  left join
  have b
  on  a.variable2 =b.variable
  order by variable;

proc sql;
 select * from want;

/* once edge case scenario where you do not have variable for variable2 it 
  works as expected giving a null value*/

data have1;
 input variable $ value variable2 $;
datalines;
V1 3 V2
V2 6 V1
 V3 4 V5
V4 1 V1
V5 5 V2
V9 8 V7
 ;

Answer 2

So you want to take the value of VARIABLE2 and use it to find the row where VARIABLE has the same value? So to do that with a MERGE statement you will want to merge by the value of VARIABLE2.

So first sort the data by VARIABLE2.

proc sort data=example2;
  by Variable2;
run;

Then make a version that just has the first two columns, but rename them so the variable name column matches but the value columns name doesn't conflict.

proc sort data=example2 (keep=Value Variable rename=(Value=New Variable=Variable2))
   out=example2b
;
  by variable2;
run;

Now you can just merge the two tables. But you only want to keep the original set of rows so use the IN= dataset option.

data want ;
   merge example2(in=in1) example2b;
   by variable2;
   if in1;
run;

If you want a more efficient method you could look into using a data step HASH object. You could load the variable/Value pairs into a hash and then use the FIND() method to check for the value associated with Variable2. If found then copy the value into a new variable. Note that you will need then re-find the value associated with VARIABLE since the previous FIND() will have overwritten VALUE.

data want ;
  set example2;
  if _n_=1 then do;
    declare hash h(dataset: 'example2');
    h.definekey('Variable');
    h.definedata('Value');
    h.definedone();
  end;
  if not h.find(key: Variable2) then new=value;
  h.find();
run;