topic Re: proc sql intersect in SAS Procedures

proc sql intersect

Mikeyjh — Mon, 31 Dec 2012 14:56:21 GMT

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.

Re: proc sql intersect

Haikuo — Mon, 31 Dec 2012 15:05:17 GMT

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

Re: proc sql intersect

PGStats — Mon, 31 Dec 2012 16:06:17 GMT

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

Re: proc sql intersect

Mikeyjh — Mon, 31 Dec 2012 16:33:03 GMT

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?

Re: proc sql intersect

PGStats — Mon, 31 Dec 2012 19:09:46 GMT

I think this would work :

proc sql;

create table tableC as

select tableA.*

from

tableA natural join

tableB(drop=sequenceId)

order by tableA.sequenceId;