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.
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
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
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?
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.