proc sql intersect

[Mikeyjh]

Hi. I have 2 tables that I would like to perform an intersect on:

create table intersect_AB as

select * from tableA

intersect

select * from tableB;

tableA and tableB are identical in structure but they both have a sequenceID column that contains a different value for otherwise equivalent rows. So for example:

tableA

col1, col2, col3 ...sequenceID

A     B     C          1

tableB

col1, col2, col3 ...sequenceID

A     B     C          2

The problem I have is I want to exclude the sequenceID column for the purpose of the "intersect" but add it back in afterwards as I need it to sort the resulting rows. (I can re-add either the sequenceID values from tableA or tableB but I need to consistantly use either A or B.) Can anyone please suggest a way to perform this? It would be great if we could do this in SQL if possible. Thanks.

[Haikuo]

Not sure how you will add sequenceID  back, but for the first step of intersect, try this:

create table intersect_AB as

select * from tableA(drop=sequenceID)

intersect

select * from tableB (drop=sequenceID) ;

Haikuo

[PGStats]

It is simpler to do this as a JOIN (you can sort at the same time) :

proc sql;

create table tableC as

select A.*

from

     tableA as A inner join

     tableB as B on A.col1=B.col1 and A.col2=B.col2 and A.col3=B.col3

order by A.sequenceId;

If you don't want to have to name the variables (col1..) then you could exclude sequenceId from tableB and use a NATURAL join

PG

[Mikeyjh]

Thanks for your comments. PGStats, I have several tables I need to perform this operation on that have different columns so I think I like the idea of a NATURAL join but this leaves me with how to re-add the sequenceID column post the join. Any thoughts on how to do this last step?

[PGStats]

I think this would work :

proc sql;

create table tableC as

select tableA.*

from

     tableA natural join

     tableB(drop=sequenceId)

order by tableA.sequenceId;

PG