topic Present (SQL) in SAS Programming

Present (SQL)

WilliamB — Tue, 03 Sep 2019 08:17:56 GMT

Hello,

I have two table I want to remove the individuals from table 1 who are not present in table 2.
Thanks for your help.

Table 1		Table 2

Nom	Argent	Nom	F
Toto	5	William	o
Titi	4	Mathias	o
Henru	3
Sophie	4
Didier	6
william	3
Nelson	4
Mathias	8




Table 3

Nom	Argent
william	3
Mathias	8

Re: Present (SQL)

PeterClemmensen — Tue, 03 Sep 2019 08:22:03 GMT

data Table1;     
input Nom $ Argent;
datalines;
Toto 5
Titi 4
Henru 3
Sophie 4
Didier 6
William 3
Nelson 4
Mathias 8
;

data Table2;
input Nom $ F $;
datalines;
William o
Mathias o
;

proc sql;
    create table Table3 as
    select * from Table1
    where Nom in (Select distinct Nom from Table2);
quit;

Re: Present (SQL)

PeterClemmensen — Tue, 03 Sep 2019 08:23:23 GMT

Or a data step approach

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

    set Table1;

    if h.find()=0;
run;