topic Re: Code for observations located in a separate table in SAS Programming

Code for observations located in a separate table

jcis7 — Wed, 27 Mar 2013 21:58:11 GMT

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!

Re: Code for observations located in a separate table

SteveNZ — Wed, 27 Mar 2013 22:04:43 GMT

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 ;

Re: Code for observations located in a separate table

RichardinOz — Wed, 27 Mar 2013 22:05:22 GMT

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

Re: Code for observations located in a separate table

RichardinOz — Wed, 27 Mar 2013 22:10:30 GMT

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

Re: Code for observations located in a separate table

DBailey — Wed, 27 Mar 2013 22:17:37 GMT

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;

Re: Code for observations located in a separate table

jcis7 — Wed, 27 Mar 2013 22:38:46 GMT

Thank you very much !