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
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 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) ;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.