topic Presence in another table in SAS Programming

Presence in another table

WilliamB — Wed, 04 Sep 2019 12:01:01 GMT

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

Re: Presence in another table

PeterClemmensen — Wed, 04 Sep 2019 12:17:26 GMT

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;

Re: Presence in another table

Kurt_Bremser — Wed, 04 Sep 2019 12:17:24 GMT

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;

Re: Presence in another table

novinosrin — Wed, 04 Sep 2019 12:20:53 GMT

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

Re: Presence in another table

SwissC — Wed, 04 Sep 2019 12:19:40 GMT

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;

Re: Presence in another table

Jagadishkatam — Wed, 04 Sep 2019 12:48:15 GMT

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;