Help using Base SAS procedures

ERROR: Subquery evaluated to more than one row

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

ERROR: Subquery evaluated to more than one row

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,

up

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;


Accepted Solutions
Solution
‎05-11-2015 04:29 AM
Super User
Super User
Posts: 7,413

Re: ERROR: Subquery evaluated to more than one row

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.

View solution in original post


All Replies
Super User
Posts: 6,962

Re: ERROR: Subquery evaluated to more than one row

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)

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 9

Re: ERROR: Subquery evaluated to more than one row

Thanks.  Muchly appreciated.

Solution
‎05-11-2015 04:29 AM
Super User
Super User
Posts: 7,413

Re: ERROR: Subquery evaluated to more than one row

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.

☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 3758 views
  • 0 likes
  • 3 in conversation