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.
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;
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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.