Hi all,
I want to create Table_C from Table_A by excluding the values found on TABLE_B based on both variables : ALPHA and BETA
Table_A has ALPHA and BETA as well as many other variables.
Table_B has only the variables ALPHA and BETA
Table_C should have less observations then Table_A.
All SQL commands I tried (like the one below) gave me 9 times more observations than expected, meaning that the cartesian product is not the one expected.
proc sql;
create table table_C as select g1.*,g2.*
from Table_A g1 left join TABLE_B g2
ON g1.ALPHA ne g2.ALPHA and g1.BETA ne g2.BETA ;
quit;
Other way to describe the problem :
I want sashelp.class without the follwing observations :
Age | weight |
---|---|
13 | 84 |
13 | 98 |
Thanks in advance for your help.
A correlated sub-query is the simplest and most explicit approach:
proc sql;
create table tableC as
select *
from TableA as A
where not exists (select * from TableB where ALPHA=A.ALPHA and BETA=A.BETA);
quit;
PG
proc sql;
create table table_C as select g1.*,g2.*
from Table_A g1 left join TABLE_B g2
ON g1.ALPHA eq g2.ALPHA and g1.BETA eq g2.BETA
where g2.beta is null;
quit;
Great, thanks. The log gave me a warning about alpha and beta being on both files, but the output is valid.
Here is a solution using a datastep or proc sql;
data table_a;
infile cards;
input age weight;
cards;
13 84
13 98
14 101
15 250
;
run;
data table_b;
infile cards;
input age weight;
cards;
13 84
13 98
;
run;
data table_c_1;
merge table_a(in=a)
table_b(in=b);
by age weight;
if a and not b;
run;
proc sql;
create table table_c_2 as
select age,weight
from table_a a where not exists (
select *
from table_b b
where a.age = b.age and
a.weight = b.weight);
A correlated sub-query is the simplest and most explicit approach:
proc sql;
create table tableC as
select *
from TableA as A
where not exists (select * from TableB where ALPHA=A.ALPHA and BETA=A.BETA);
quit;
PG
Thanks, I was aware of the if a and not b in the data-merge but I wanted to avoid the sorting on huge files.
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.