topic Re: ERROR: Subquery evaluated to more than one row. in SAS Programming

ERROR: Subquery evaluated to more than one row.

radha009 — Mon, 30 Apr 2018 14:03:14 GMT

Can you please help: i want to create a new column with type for matching Id. I am getting the error as ERROR: Subquery evaluated to more than one row

Proc sql;
alter table dash.fruits
add type char(30) format = $30.;
update dash.fruits as A
set Type = (select b.type from dash.category as b
where A.id-b.id);
quit;

Re: ERROR: Subquery evaluated to more than one row.

Shmuel — Mon, 30 Apr 2018 14:08:53 GMT

Did you mean: where A.id = b.id); - you have a hyphen instead equal sign.

Re: ERROR: Subquery evaluated to more than one row.

radha009 — Mon, 30 Apr 2018 14:10:52 GMT

oops typo. i still got the same error

Re: ERROR: Subquery evaluated to more than one row.

RW9 — Mon, 30 Apr 2018 14:12:38 GMT

The error tells you everything you need to know, the subquery:

(select b.type from dash.category as b
where A.id-b.id)

Returned more than one row of data. Check in dash.category, maybe you have duplicate rows, or more than one merge on id. This is where showing some test data in the form of a datastep really helps!

Re: ERROR: Subquery evaluated to more than one row.

emrancaan — Mon, 30 Apr 2018 14:18:15 GMT

Proc sql;
alter table dash.fruits
add type char(30) format = $30.;
update dash.fruits as A
set Type = (select DISTINCT b.type from dash.category as b
                  where A.id=b.id);
quit;