Code for observations located in a separate table

[jcis7]

Hello,

I've two tables.

TableA has all observations.

TableB has 30 observations, all of which are in TableA.

They can be matched by an 'ID'

How do I create a new variable in TableA indicating that all the observations in TableB are 'late' (ie., new variable name = late; all IDs found in TableB are coded

with late=1 in TableA).

I know a long way:  use a data step, and use).  Is there a way to do using subquery in SQL?

data new;

     set tableA;

     late=0;

      if ID in ('0223', '0123', '0124' etc) then late=1;

run;

Thanks!

[SteveNZ]

Many ways to do it but easy one is:

data tableb ;

set tableb ;

    late = 'late' ;

run ;

proc sql ;

    create table new as

        select A.*,B.id, B.late

        from tableA as A left join tableB as B

        on A.id = B.id ;

quit ;

[RichardinOz]

One pass for SQL, which is better if the data is not sorted:

Proc SQL ;

     Create Table want as

          Select A.*

               ,     Case

                         When B.ID IS NOT NULL

                         Then 1

                         Else 0

                    End

                    As late

          From     A

               Left join B

               On A.ID = B.ID

          Order by A.ID

     ;

(Untested code)

Richard

[RichardinOz · Accepted solution]

Assuming both datasets are ordered by ID, the data step version of what i think you want is

Data want ;

     Merge A B (Keep = ID In = InB) ;

     By ID ;

     late  = InB ;

Run ;

Richard

[jcis7]

Thank you very much !

[DBailey]

without sorting or recoding...

proc sql;

select

     t1.*,

     case when t2.id is not null then 1 else 0 end as Late

from

     tablea t1

     left outer join tableb t2

       on t1.id=t2.id;

quit;