topic Flag for observations of a variable in one table if in another table in SAS Programming

Flag for observations of a variable in one table if in another table

KPCklebspn — Wed, 10 Jun 2020 02:14:31 GMT

Hi all, I have 2 tables

tableA

ID var1

1 a

2 b

3 c

tableB

ID var1

1 b

4 c

I want to flag the ID in tableA if the ID is in tableB

want

ID var1 flag

1 a 1

2 b 0

3 c 0

Is there a way to do this with proc sql?

proc sql;
create table as want as
select * from tableA
**not sure what to put here***
quit;

Re: Flag for observations of a variable in one table if in another table

novinosrin — Wed, 10 Jun 2020 02:24:48 GMT


data a;
input ID   var1	$;
cards;
1      a

2      b

3      c
;

data b;
input ID   var1	$;
cards;

1         b

4         c
;

proc sql;
create table want  as
select a.* ,a.id=b.id as flag
from a a left join b b
on a.id=b.id;
quit;

Re: Flag for observations of a variable in one table if in another table

smantha — Wed, 10 Jun 2020 02:32:19 GMT

Proc sql;
Create table x (drop = id2)
As select *, case when id=id2 then 1
Else .
End as flag
From
(Select a.*, b.id as id2 from a left join b
On a.id = b. Id)
Quit;

Re: Flag for observations of a variable in one table if in another table

r_behata — Wed, 10 Jun 2020 02:36:18 GMT

data tablea;
input id var1 $;
cards;
1 a
2 b
3 c
;
run;

data tableb;
input id var1 $;
cards;
1 b
4 c
;
run;

proc sql;
create table want as
select a.*,
	 case when b.id then 1
	 else 0
	 end as flag
from tablea a
left join tableb b
on a.id=b.id;
quit;

Re: Flag for observations of a variable in one table if in another table

Ksharp — Wed, 10 Jun 2020 12:00:32 GMT

data a;
input ID   var1	$;
cards;
1      a
2      b
3      c
;

data b;
input ID   var1	$;
cards;
1         b
4         c
;

proc sql;
create table want  as
select a.* ,exists(select * from b where id=a.id) as flag
from a ;
quit;