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
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):
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) ?
Yes, I finally noticed what I was doing wrong. Thank you so much for responding!
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)
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.