BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
salvavit
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
RichardDeVen
Barite | Level 11

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

 

View solution in original post

1 REPLY 1
RichardDeVen
Barite | Level 11

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

 

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 890 views
  • 0 likes
  • 2 in conversation