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

ERROR: Subquery evaluated to more than one row

sasbanker — Sun, 10 May 2015 21:00:38 GMT

I have 2 tables, WORK.HRIS_OR and WORK.FAKC_OR.

Both tables have the key field Standard_ID.

Both tables are sort by Standard_ID, and have no duplicate Standard_ID's.

I want to update the WORK.HRIS_OR table values with the values from the corresponding fields in WORK.FAKC_OR.

I'm getting the error "ERROR: Subquery evaluated to more than one row"

I'm a newbie with no training and little experience, so please be specific in your response.

Thanks,

WORK.HRIS_OR created, with 274,147 rows

WORK.FAKC_OR created, with 409 rows

21276 /* Updates HRIS_OR Table with FAKC_OR Table Values */

21277 proc sql;

21278 update HRIS_OR

21279 set

21280 AltLob = (select AltLob from FAKC_OR),

21281 Organization = (select Organization from FAKC_OR),

21282 Line_of_Business_Name = (select Line_of_Business_Name from FAKC_OR),

21283 Sub_Line_of_Business_Name = (select Sub_Line_of_Business_Name from FAKC_OR),

21284 Division_Name = (select Division_Name from FAKC_OR),

21285 Sub_Division_Name = (select Sub_Division_Name from FAKC_OR)

21286 where Standard_ID in (select Standard_ID from FAKC_OR);

ERROR: Subquery evaluated to more than one row.

21287 quit;

Re: ERROR: Subquery evaluated to more than one row

Kurt_Bremser — Mon, 11 May 2015 06:12:55 GMT

A subquery like

select Sub_Division_Name from FAKC_OR

will give you all Sub_Division_Name from the table in a list, but you need only a single value here for the set, so you would need to specify which observation from FAKC_OR you want selected. Unpractical to do it this way.

For what you want to do, have a look at

Step-by-Step Programming with Base SAS(R) Software

(UPDATE in a data step)

and

SAS(R) 9.2 SQL Procedure User's Guide

(UPDATE in SQL)

Re: ERROR: Subquery evaluated to more than one row

RW9 — Mon, 11 May 2015 08:29:23 GMT

The key to your question lies in this statement:

"I want to update the WORK.HRIS_OR table values with the values from the corresponding fields in WORK.FAKC_OR."

You haven't specified the rows to be updated, this for example:

AltLob = (select AltLob from FAKC_OR),

Selects every record from FAKC_OR and tries to assign it to one record in HRIS_OR which is why you get the fail.

Not knowing your data I could suggest:

proc sql;

update HRIS_OR A

set AltLob=(select AltLob from FAKC_OR where ID=A.ID and SOME_OTHER_ID=A.SOME_OTHER_ID),

...

I would suggest however that you do a more conditional procedure:

proc sql;

create table WANT as

select COALESCE(A.ID,B.ID) as ID,

case when A.VALUE is null then B.VALUE

else A.VALUE end as VALUE,

...

from HRIS_OR A

full join FAKC_OR B

on A.ID=B.ID;

quit;

This way you have more control over what gets populated, for instance if you have missing data in the new data but not in the old etc.

Re: ERROR: Subquery evaluated to more than one row

sasbanker — Mon, 11 May 2015 12:24:06 GMT

Thanks. Muchly appreciated.