create a new column,by finding value in a column B and return its corresponding value from next column A

[boin]

I have two variables

Employee       Manager

Sam               Robert

Robert             Daniel

Greg                Taylor

Taylor              Mark

I want to create a new  variables which should be like this

Twoup     Oneup     Employee

Daniel      Robert     Sam

Please advise ways to get this output.

Kind Regards

Boin

[ballardw]

Proc SQL is one solution. .

Proc Sql;

     create table Supervisors as

     select b.manager as TwoUp, a.Manager as OneUP, a.Employee

     from datasetname as a left join datasetname as b on

          a.manager = b.Employee

    ;

quit;

[Haikuo]

Give another hash ,

data have;

input employee :$8. manager:$8.;

cards;

Sam               Robert

Robert             Daniel

Greg                Taylor

Taylor              Mark

;

run;quit;

data want;

length twoup oneup  $ 8;

  if _n_=1 then do;

    if 0 then set have(rename=manager=_m);

    declare hash h(dataset:'have(rename=(manager=_m))');

      h.definekey('employee');

      h.definedata('_m');

      h.definedone();

end;

set have;

   if h.find()=0 then do;

      oneup=_m;

        if h.find(key:oneup)=0 then twoup=_m;

      end;

      drop _m manager;

run;

Haikuo

[boin]

thanks  Haikuo, it works