Presence in another table

[WilliamB]

Hello,

I have two tables, I would like to complete the first with a new variable. If "Name" is present in the second table then Pres = 1 otherwise 0.

Table 1			Table 2
Name	Fonction		Name	Agence	Desk
William	A		William	er	A2
Mathias	B		Fabien	qs	A3
Nelson	Y		Luise	df	A4
			Rose	gh	B25
			Mathias	te	B26
			Sebastien	jk	C15
			Jonathan	lm	C16
			Arthur	nb	B29
			Julien	az	B30
I want to get to that:
Name	Fonction	Pres
William	A	1
Mathias	B	1
Nelson	Y	0

Thanks for your help

[PeterClemmensen]

data Table1;
input Name :$20. Fonction $1.;
datalines;
William A
Mathias B
Nelson Y
;
   
data Table2;
input Name :$20. Agence $ Desk $;
datalines;
William er A2
Fabien qs A3
Luise df A4
Rose gh B25
Mathias te B26
Sebastien jk C15
Jonathan lm C16
Arthur nb B29
Julien az B30
;

data want;
    if _N_=1 then do;
        declare hash h(dataset:'Table2');
        h.definekey('Name');
        h.definedone();
    end;

    set Table1;

    Pres=ifn(h.check(), 0, 1);
run;

[Kurt_Bremser · Accepted solution]

Straightforward with SQL:

proc sql;
create table want1 as
select
  a.name,
  a.function,
  case
    when name in (select b.name from table_2 b)
    then 1
    else 0
  end as pres
from table_1 a;
quit;

[novinosrin]

Hello @WilliamB  Another way, is to use boolean expressions when want in 1,0s  a method heavily used , taught and emphasized by @SASKiwi  in many of his briiliant posts

For example,

data Table1;
input Name :$20. Fonction $1.;
datalines;
William A
Mathias B
Nelson Y
;
   
data Table2;
input Name :$20. Agence $ Desk $;
datalines;
William er A2
Fabien qs A3
Luise df A4
Rose gh B25
Mathias te B26
Sebastien jk C15
Jonathan lm C16
Arthur nb B29
Julien az B30
;

proc sql;
create table want as
select a.*,a.name=b.name as Pres
from table1 a left join table2 b
on a.name=b.name;
quit;

Since it appears your name values are unique in both tables, m*n product combo in a join is not a worry and makes it rather robust and easy 

[SwissC]

PROC SQL;
  CREATE TABLE want AS SELECT
  a.*, 
  CASE
    WHEN b.pres1=1 THEN 1
    ELSE 0
  END as pres
  FROM have1 a
  LEFT JOIN (select distinct name, 1 as pres1 from have2) b
    ON a.name=b.name;
QUIT;

[Jagadishkatam]

Alternatively with merge statement

data Table1;
input Name :$20. Fonction $1.;
datalines;
William A
Mathias B
Nelson Y
;
   
data Table2;
input Name :$20. Agence $ Desk $;
datalines;
William er A2
Fabien qs A3
Luise df A4
Rose gh B25
Mathias te B26
Sebastien jk C15
Jonathan lm C16
Arthur nb B29
Julien az B30
;

proc sort data=table1;
by name;
run;

proc sort data=table2;
by name;
run;

data want;
merge Table1(in=a) Table2(in=b keep=name);
by name ;
if a;
if a and b then pres=1;
else pres=0;
run;