DATA Step, Macro, Functions and more

Subquery evaluated to more than one row

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 120
Accepted Solution

Subquery evaluated to more than one row

[ Edited ]

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.

 

Swain

Accepted Solutions
Solution
2 weeks ago
Super User
Posts: 10,278

Re: Subquery evaluated to more than one row

Posted in reply to DeepakSwain

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Solution
2 weeks ago
Super User
Posts: 10,278

Re: Subquery evaluated to more than one row

Posted in reply to DeepakSwain

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 50 views
  • 1 like
  • 2 in conversation