topic Re: add flag if an observation has a common observation in another dataset in New SAS User

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

Jedrzej — Fri, 29 Mar 2019 10:20:21 GMT

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

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

yabwon — Fri, 29 Mar 2019 10:52:48 GMT

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

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

Kurt_Bremser — Fri, 29 Mar 2019 11:41:14 GMT

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;

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

Ksharp — Fri, 29 Mar 2019 11:47:32 GMT

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;

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

novinosrin — Fri, 29 Mar 2019 12:50:37 GMT

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;