Help resolving 'ERROR: Subquery evaluated to more than one row' in a program

adamsfam1 — Mon, 17 Jul 2017 22:01:07 GMT

I'm running SAS 9.2. I'm trying to run a rather long query using mostly Proc SQL (and a few data steps) where I'm pulling data from several tables (medical/RX/eligibility/capitation - 8 in total). I'm merging them togerther in a data step near the end, creating a final Proc SQL with the merged data. I'm also using cards to import over 2,000 member ids to narrow down the criteria to a certain division of people from an account in a separate query. And finally using ODS for emailing the output. I keep getting the the following error code:

NOTE: SAS threaded sort was used.
ERROR: Subquery evaluated to more than one row.

I've tried putting my select from census where clause in multiple places in the program, but it just doens't work anywhere. I've put it in all the main extract pulls in the program and I still get the same error code. There could be an added kink in the eligibilty section because we have to count subscribers and members from the member pull - so I need to be able to use the cards members - see below:

/************************ Membership **************************/

proc sql;
CREATE TABLE WORK.ppo AS
SELECT

a.midsu_acct_num,
a.midsu_brnch_cd,
a.mbr_num,
substr(a.mbr_num,1,9) as ssn,
a.mbr_mth_yr_mth ,
b.brnch_nm,
b.BEN_PLAN_NM

from hce.mbr_mth_ppo_indem a

left join hce.brnch_ben_pln b
on a.midsu_src_sys_cd = b.midsu_src_sys_cd and
a.midsu_acct_num = b.midsu_acct_num and
a.midsu_brnch_cd = b.midsu_brnch_cd and
a.ben_plan_cd = b.ben_plan_cd

where
&acctnum and
a.mbr_mth_yr_mth between &datea and &dateb

;
quit;

proc sql;
CREATE TABLE WORK.claim AS
SELECT

a.midsu_acct_num,
a.midsu_brnch_cd,
a.mbr_num,
substr(a.mbr_num,1,9) as ssn,
a.mbr_mth_yr_mth ,
b.brnch_nm,
b.BEN_PLAN_NM

from hce.member_month a

left join hce.brnch_ben_pln b
on a.midsu_src_sys_cd = b.midsu_src_sys_cd and
a.midsu_acct_num = b.midsu_acct_num and
a.midsu_brnch_cd = b.midsu_brnch_cd and
a.ben_plan_cd = b.ben_plan_cd

where
&acctnum and
a.mbr_mth_yr_mth between &datea and &dateb

;
quit;

data work.member;
set work.claim
work.ppo;
run;

proc sql;
create table work.members as
select

mbr_num,
ssn,
mbr_mth_yr_mth as pd_month,
BEN_PLAN_NM,
brnch_nm,

count(distinct ssn) as subscribers,
count(distinct mbr_num) as members,

sum(0) as rx_in_net_pay,
sum(0) as rx_out_net_pay,
sum(0) as rx_total ,
sum(0) as med_in_net_pay,
sum(0) as med_out_net_pay,
sum(0) as med_total,
sum(0) as cap_pay

from work.member

where (select mbr_num from jmada2.mcafee_census) or
(select ssn from jmada2.mcafee_census2)

group by 1,2,3,4,5;
quit;

All of my SQLs have mbr_num in them and displayed. Below is the part of my code where the code happens. These are the last 2 SQLs from my query. Everything runs until this point. I'm pulling in a select statment where I've used cards to create a dataset with the 2000+ member ids. The first SQL with the where clause is the part where I get the errror. The second SQL will be the final output for the ODS. If I need to provide the entire program, please let me know. It errored out when I tried to submit with the program attached.

proc sql;

create table work.member_select as

select

mbr_num,

pd_month,

BEN_PLAN_NM,

brnch_nm,

sum(subscribers) as subscribers,

sum(members) as members,

sum(rx_in_net_pay) as rx_in_net_pay,

sum(rx_out_net_pay) as rx_out_net_pay,

sum(rx_total) as rx_total ,

sum(med_in_net_pay) as med_in_net_pay,

sum(med_out_net_pay) as med_out_net_pay,

sum(med_total) as med_total,

sum(cap_pay) as cap_pay

from work.comb

where (select mbr_num from jmada2.mcafee_census)

group by 1,2,3,4;

quit;

proc sql;

create table work.final as

select

pd_month,

BEN_PLAN_NM,

brnch_nm,

sum(subscribers) as subscribers,

sum(members) as members,

sum(rx_in_net_pay) as rx_in_net_pay,

sum(rx_out_net_pay) as rx_out_net_pay,

sum(rx_total) as rx_total ,

sum(med_in_net_pay) as med_in_net_pay,

sum(med_out_net_pay) as med_out_net_pay,

sum(med_total) as med_total,

sum(cap_pay) as cap_pay

from work.member_select

group by 1,2,3;

quit;

Here is the part of my log with the error:

+proc sql;
495 +create table work.member_select as
496 +select
497 +
498 +
499 +mbr_num,
500 +pd_month,
501 +BEN_PLAN_NM,
502 +brnch_nm,
503 +
504 +
505 +sum(subscribers) as subscribers,
506 + sum(members) as members,
507 +
508 +sum(rx_in_net_pay) as rx_in_net_pay,
509 +sum(rx_out_net_pay) as rx_out_net_pay,
510 +sum(rx_total) as rx_total ,
511 +sum(med_in_net_pay) as med_in_net_pay,
512 +sum(med_out_net_pay) as med_out_net_pay,
513 +sum(med_total) as med_total,
514 +sum(cap_pay) as cap_pay
515 +
516 +from work.comb
517 +
518 +where (select mbr_num from jmada2.mcafee_census)
519 +group by 1,2,3,4;
ERROR: The following columns were not found in the contributing tables: mbr_num.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of
statements.
520 +quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
24 The SAS System 13:55 Monday, July 17, 2017

Re: Help resolving 'ERROR: Subquery evaluated to more than one row' in a program

adamsfam1 — Mon, 17 Jul 2017 23:55:35 GMT

I accidentally put in the log text from the wrong run. Below is the correct log text with the correct error code. I apologize!

496 +
497 +proc sql;
498 +create table work.member_select as
499 +select
500 +
501 +
502 +mbr_num,
503 +pd_month,
504 +BEN_PLAN_NM,
505 +brnch_nm,
506 +
507 +
508 +sum(subscribers) as subscribers,
509 + sum(members) as members,
510 +
511 +sum(rx_in_net_pay) as rx_in_net_pay,
512 +sum(rx_out_net_pay) as rx_out_net_pay,
513 +sum(rx_total) as rx_total ,
514 +sum(med_in_net_pay) as med_in_net_pay,
515 +sum(med_out_net_pay) as med_out_net_pay,
516 +sum(med_total) as med_total,
517 +sum(cap_pay) as cap_pay
518 +
519 +from work.comb
520 +
521 +where (select mbr_num from jmada2.mcafee_census)
522 +group by 1,2,3,4;
NOTE: SAS threaded sort was used.
ERROR: Subquery evaluated to more than one row.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of
statements.
523 +quit;
24 The SAS System 14:16 Monday, July 17,
2017

NOTE: The SAS System stopped processing this step because of errors.
NOTE: SAS set option OBS=0 and will continue to check statements.
This might cause NOTE: No observations in data set.
NOTE: PROCEDURE SQL used (Total process time):

Re: Help resolving 'ERROR: Subquery evaluated to more than one row' in a program

ChrisNZ — Tue, 18 Jul 2017 01:21:59 GMT

521 +where (select mbr_num from jmada2.mcafee_census)

returns many rows

Did you mean: where mbr_num in (select mbr_num from jmada2.mcafee_census) ?

Re: Help resolving 'ERROR: Subquery evaluated to more than one row' in a program

adamsfam1 — Tue, 18 Jul 2017 02:13:32 GMT

No need for anyone to respond, unless you have an easier way to pull the data.

I was finally able to get this program to run with this change is my where clause

where mbr_num in (select mbr_num from jmada2.mcafee_census)

Re: Help resolving 'ERROR: Subquery evaluated to more than one row' in a program

adamsfam1 — Tue, 18 Jul 2017 02:15:08 GMT

Yes, I finally noticed what I was doing wrong. Thank you so much for responding!

topic Re: Help resolving 'ERROR: Subquery evaluated to more than one row' in a program in SAS Programming

Help resolving 'ERROR: Subquery evaluated to more than one row' in a program

Re: Help resolving 'ERROR: Subquery evaluated to more than one row' in a program

Re: Help resolving 'ERROR: Subquery evaluated to more than one row' in a program

Re: Help resolving 'ERROR: Subquery evaluated to more than one row' in a program

Re: Help resolving 'ERROR: Subquery evaluated to more than one row' in a program