Help using Base SAS procedures

PROC SQL-Combining data from multiple tables

Accepted Solution Solved
Reply
Contributor
Posts: 34
Accepted Solution

PROC SQL-Combining data from multiple tables

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 :

Ageweight
1384
1398

Thanks in advance for your help.


Accepted Solutions
Solution
‎06-30-2015 01:14 PM
Respected Advisor
Posts: 4,654

Re: PROC SQL-Combining data from multiple tables

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

PG

View solution in original post


All Replies
Super Contributor
Posts: 578

Re: PROC SQL-Combining data from multiple tables

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;

Contributor
Posts: 34

Re: PROC SQL-Combining data from multiple tables

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

Valued Guide
Posts: 858

Re: PROC SQL-Combining data from multiple tables

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

Solution
‎06-30-2015 01:14 PM
Respected Advisor
Posts: 4,654

Re: PROC SQL-Combining data from multiple tables

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

PG
Contributor
Posts: 34

Re: PROC SQL-Combining data from multiple tables

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 299 views
  • 6 likes
  • 4 in conversation