CREATE TABLE BASED ON MULTIPLE CONDITIONS

salvavit — Wed, 27 May 2020 12:59:24 GMT

Hi all,

I am trying to create a table as a copy of a "father table" based on two WHERE conditions that make reference to a "parent table". In particular, I've wrote:

proc sql;
create table SON_TABLE as
select *
from FATHER_TABLE
where
person_ID not in (select person_ID from PARENT_TABLE)
and
DATE not in (select DATE from PARENT_TABLE); 

quit;

I know for sure that the table should be populated but it shows zero rows instead. I guess something is wrong in how the conditions are stated.

May anyone explain me how can i fix this?

Thank you in advance

Re: CREATE TABLE BASED ON MULTIPLE CONDITIONS

RichardDeVen — Wed, 27 May 2020 13:07:37 GMT

SAS SQL does not have what is known as a in-tuple. You will need to create a surrogate for the dual requirement of person AND their corresponding date being in some likewise list. The easiest surrogate is a string concatenation.

create table SON_TABLE as
select *
from FATHER_TABLE
where
  catx('~', person_ID, date) not in 
    (select catx('~', person_ID, date) from PARENT_TABLE)
;

topic Re: CREATE TABLE BASED ON MULTIPLE CONDITIONS in SAS Programming

CREATE TABLE BASED ON MULTIPLE CONDITIONS

Re: CREATE TABLE BASED ON MULTIPLE CONDITIONS