add flag if an observation has a common observation in another dataset

[Jedrzej]

hi  i have two datasets A and B which could have common variable ID. I want to add a flag "Y" to the dataset A if the same ID existis in dataset B

example

A         B

ID        ID

1          2

2  Y      6      

3          4

4  Y     10

[yabwon]

Hi,

try something like this:

data A;
input id;
cards;    
1
2
3
4
;
run;

data B;
input id;
cards;
2
6
4
10
;
run;

data A;
  if 0 then set A;
  declare hash H(dataset:"B(keep=ID)");
  H.DefineKey("ID");
  H.DefineDone();

  do until(eof);
    set A end = eof;
    if H.check()=0 then flag = "Y";
                   else flag = " ";
    output;
  end;
  stop;
run;

All the best

Bart

[Kurt_Bremser]

Another solution: use a format:

data a;
input id $;
cards;
1
2
3
4
;
run;

data b;
input id $;
cards;
2
6
4
10
;
run;

data cntlin;
set b (rename=(id=start)) end=eof;
fmtname = 'lookup';
type = 'C';
label = 'Y';
output;
if eof
then do;
  hlo = 'O';
  start = 'OTHER';
  label = ' ';
  output;
end;
run;

proc format cntlin=cntlin;
run;

data want;
set a;
flag = put(id,$lookup.);
run;

[Ksharp]

data A;
input id;
cards;    
1
2
3
4
;
run;

data B;
input id;
cards;
2
6
4
10
;
run;
proc sql;
create table want as
select *,exists(select * from b where id=a.id) as flag
 from a;
quit;

[novinosrin · Accepted solution]

data A;
input id;
cards;    
1
2
3
4
;
run;

data B;
input id;
cards;
2
6
4
10
;

proc sql;
create table want as
select a.*,ifc(a.id=b.id,'Y',' ') as Flag
from a a left join b b
on a.id=b.id;
quit;