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
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;
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;
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;
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
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;
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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: