Help using Base SAS procedures

proc sql intersect

Reply
Contributor
Posts: 28

proc sql intersect

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.

Respected Advisor
Posts: 3,124

Re: proc sql intersect

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

Respected Advisor
Posts: 4,651

Re: proc sql intersect

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

PG
Contributor
Posts: 28

Re: proc sql intersect

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?

Respected Advisor
Posts: 4,651

Re: proc sql intersect

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

PG
Ask a Question
Discussion stats
  • 4 replies
  • 956 views
  • 0 likes
  • 3 in conversation