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
... View more