Code for observations located in a separate table

Accepted Solution Solved
Reply
Regular Contributor
Posts: 192
Accepted Solution

Code for observations located in a separate table

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!



Accepted Solutions
Solution
‎03-27-2013 06:05 PM
Super Contributor
Posts: 644

Re: Code for observations located in a separate table

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

View solution in original post


All Replies
Frequent Contributor
Posts: 87

Re: Code for observations located in a separate table

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 ;

Super Contributor
Posts: 644

Re: Code for observations located in a separate table

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

Solution
‎03-27-2013 06:05 PM
Super Contributor
Posts: 644

Re: Code for observations located in a separate table

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

Regular Contributor
Posts: 192

Re: Code for observations located in a separate table

Thank you very much !

Super Contributor
Posts: 578

Re: Code for observations located in a separate table

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;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 213 views
  • 3 likes
  • 4 in conversation