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

Reply
Contributor
Posts: 44

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

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


Super User
Posts: 10,550

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

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;

Respected Advisor
Posts: 3,124

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

Give another hash Smiley Happy,

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(keySmiley Surprisedneup)=0 then twoup=_m;

      end;

      drop _m manager;

run;


Haikuo

Contributor
Posts: 44

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

thanks  Haikuo, it works

Ask a Question
Discussion stats
  • 3 replies
  • 347 views
  • 1 like
  • 3 in conversation