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

Reply
Occasional Contributor
Posts: 8

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

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

Occasional Contributor
Posts: 8

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

Posted in reply to adamsfam1

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):

PROC Star
Posts: 1,760

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

Posted in reply to adamsfam1

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) ?

Occasional Contributor
Posts: 8

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

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

Occasional Contributor
Posts: 8

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

Posted in reply to adamsfam1

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)

Ask a Question
Discussion stats
  • 4 replies
  • 275 views
  • 0 likes
  • 2 in conversation