Subquery evaluated to more than one row

[DeepakSwain]

Hi there,

I am trying to select some records from my master table (e.g. table_all) having all records excluding all records available in table_Y and table_P. I am getting the error message:  Subquery evaluated to more than one row. 

data table_all;
input id status $  ;
datalines;
101 F
102 C
103 F
104 F
104 C
105 F
106 F
106 C
108 F
109 C
;
run;


data table_Y;
input id Consult $ ;
datalines;
101 Y
104 Y
;
run;


data table_P;
input id type $;
datalines;
104 P
106 P
109 P
;
run;


data table_F_Not_Y_Not_P;
input id status $ ;
datalines;
103 F
105 F
108 F
;
run;


proc sql;
create table want as
select *
from table_all 
where id ne (select id from table_Y) and id ne (select id from table_P);
quit;

/*WANT=table_F_Not_Y_Not_P*/

Thanks in advance for your kind reply to overcome the error message.

[Kurt_Bremser · Accepted solution]

Just use syntax that allows for multiple results of the sub-select:

proc sql;
create table want as
select *
from table_all 
where id not in (select id from table_Y) and id not in (select id from table_P);
quit;