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

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

boin — Fri, 28 Feb 2014 23:40:25 GMT

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

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

ballardw — Fri, 28 Feb 2014 23:58:38 GMT

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;

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

Haikuo — Sat, 01 Mar 2014 00:43:21 GMT

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

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

boin — Tue, 04 Mar 2014 10:45:50 GMT

thanks Haikuo, it works