velvetrock velvetrock - 5 months ago 10
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

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

Table1:

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


Table2:

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


Expected result:

Table1:

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;
quit;


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

Hope to get your answer. Thanks!

Answer

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;
Comments