velvetrock velvetrock - 1 year ago 70
SQL Question

SAS create a flag in table1 if variable is present in table2

I have two tables about the course of each student in 2 classes, and I want to create a binary variable

in Table1 which presents the presence of variable
in Table2 for each student.


class student course
1 A 001
1 A 004
2 B 003


class student course
1 A 002
1 A 004
2 B 003

Expected result:


class student course flag
1 A 001 0
1 A 004 1
2 B 003 1

I've tried the suivant program:

proc sql;
create table common as
select A.*, B.*
from Table1 A
inner join Table2 B
on A.class=B.class and A.student=B.student and A.course=B.course;

That only outputs the rows in common and I didn't succeed to create a flag.

Hope to get your answer. Thanks!

Answer Source

Here is one method:

proc sql:
    create table common as
        select a.*,
               (case when exists (select 1 from table2 b where A.class=B.class and A.student=B.student and A.course=B.course)
                     then 1 else 0
                end) as flag
        from table1 a;