topic PROC SQL-Combining data from multiple tables in SAS Procedures

PROC SQL-Combining data from multiple tables

nketata — Tue, 30 Jun 2015 15:55:54 GMT

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;

Age	weight
13	84
13	98

Re: PROC SQL-Combining data from multiple tables

DBailey — Tue, 30 Jun 2015 15:58:01 GMT

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;

Re: PROC SQL-Combining data from multiple tables

nketata — Tue, 30 Jun 2015 16:15:54 GMT

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

Re: PROC SQL-Combining data from multiple tables

Steelers_In_DC — Tue, 30 Jun 2015 16:24:25 GMT

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

Re: PROC SQL-Combining data from multiple tables

PGStats — Tue, 30 Jun 2015 17:13:59 GMT

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;

Re: PROC SQL-Combining data from multiple tables

nketata — Tue, 30 Jun 2015 17:44:12 GMT

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.