Zap Zap - 2 months ago 13
SQL Question

SAS: Left join works for defined numbers but not for a selected list of numbers

let's say I have those three tables:

data Con1;
input ID Age;
cards;
11 22
23 32
32 3
14 73
;
run;

data Con2;
input ID Age;
cards;
11 22
23 32
32 3
14 73
15 11
7 15
;
run;

data Con3;
input ID Height;
cards;
11 150
23 180
32 181
14 175
15 149
7 159
;
run;


Now I want to make a left join between
Con2
and
Con3
but only for the rows that are not in
Con1
.

proc sql;
create table works as
select *
from Con2 where ID not in (select ID from Con1);
run;

proc sql;
create table want as
select a.*, b.Height from works as a
left join Con3 as b
on a.ID=b.ID;
run;


This works fine, but I don't like that fact that I have needed two steps.

So my goal is to get it in only one step that should look somehow like this:

proc sql;
create table want_fails as
select
a.*,
b.Height
from Con2 (where=(ID not in (select ID from Con1))) as a
left join Con3 as b
on a.ID=b.ID;
run;


And I just don't understand how this resolves in an error, when on the other hand the following statement is working just fine:

proc sql;
create table want_works as
select
a.*,
b.Height
from Con2 (where=(ID not in (11 23 32 14))) as a
left join Con3 as b
on a.ID=b.ID;
run;


Here is the error:

ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant, a datetime constant,
a missing value, -.

ERROR 76-322: Syntax error, statement will be ignored. ERROR: Syntax error while parsing WHERE clause. ERROR: Invalid value for the WHERE option. ERROR: Some options for file WORK.CON2 were not processed because of errors or warnings noted above.


So can you please tell me what I'm overlooking?

I only have another workaroud with a
create view
but I prefere the solution from @user2877959.

proc sql;
create view test as
select *
from Con2 where ID not in (select ID from Con1);
create table want_works2 as
select
a.*,b.Height from test as a
left join Con3 as b
on a.ID=b.ID;

Answer Source

You cannot have a subquery in a where= dataset option.

Does this not do what you want?

proc sql;
  create table want_works as
  select a.*,
         b.Height
  from Con2 as a
  left join Con3 as b
    on a.ID=b.ID
  where a.ID not in (select ID from Con1);
run;