PROC SQL-Combining data from multiple tables

[nketata]

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.

[DBailey]

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;

[nketata]

Great, thanks. The log gave me a warning about alpha and beta being on both files, but the output is valid.

[Steelers_In_DC]

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);

[PGStats · Accepted solution]

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

[nketata]

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.