CODEWITHSUNDEEP
sqlsascompareflags
velvetrock
velvetrock

Reputation: 585

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!

Upvotes: 1

Views: 4137

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

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;

Upvotes: 3

Tom
Tom

Reputation: 51581

Just use a MERGE and the IN= dataset options.

 data want ;
   merge table1(in=in1) table2(in=in2);
   by class student course;
   if in1 ;
   flag=in2;
 run;

Upvotes: 3

Related Questions