Pyrite | Level 9

## 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!

1 ACCEPTED SOLUTION

Accepted Solutions
Quartz | Level 8

## 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

5 REPLIES 5
Obsidian | Level 7

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

Quartz | Level 8

## 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

Quartz | Level 8

## 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

Pyrite | Level 9

## Re: Code for observations located in a separate table

Thank you very much !

Lapis Lazuli | Level 10

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

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