BookmarkSubscribeRSS Feed
adamsfam1
Calcite | Level 5

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

4 REPLIES 4
adamsfam1
Calcite | Level 5

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

ChrisNZ
Tourmaline | Level 20

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

adamsfam1
Calcite | Level 5

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

adamsfam1
Calcite | Level 5

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)

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1637 views
  • 0 likes
  • 2 in conversation