BookmarkSubscribeRSS Feed
tmm
Fluorite | Level 6 tmm
Fluorite | Level 6

When I run the following query, I get the response of subquery evaulated to more than one row. mbr_sys_id = XXXX fst_srvc_dt = xxxx

proc sql;

update cardiac.pos2 a

   set pl_of_srvc_sys_id = (select pl_of_srvc_sys_id from cardiac.pos b

where a.mbr_sys_id=b.mbr_sys_id and a.fst_srvc_dt = b.fst_srvc_dt),

pl_of_srvc_sum_desc = (select pl_of_srvc_sum_desc from cardiac.pos b

where a.mbr_sys_id=b.mbr_sys_id and a.fst_srvc_dt = b.fst_srvc_dt)

where a.pl_of_srvc_sys_id is null and pl_of_srvc_sum_desc is null;

run;

5 REPLIES 5
DBailey
Lapis Lazuli | Level 10

Have you checked to see if the query

select * from cardiac.pos b where mbr_sys_id=XXX and fst_srvc_dt=xxx

returns multiple rows?

tmm
Fluorite | Level 6 tmm
Fluorite | Level 6

Yes it does have multiple rows. I cannot change the multiple rows within the table being updated because it has to be as it is.

DBailey
Lapis Lazuli | Level 10

How would you determine which of the multiple rows to use for the update?

tmm
Fluorite | Level 6 tmm
Fluorite | Level 6

Nevermind. I changed it to the following because the reason for multiple member ids is we have various provider tins and mpins that serviced the member. So, I had to pull in a table that says give me member, tin, mpin place of service and service desc. so i did it this way instead with the update

proc sql;

update cardiac.pos2 a

   set pl_of_srvc_sys_id = (select pl_of_srvc_sys_id from cardiac.pos b

where a.mbr_sys_id=b.mbr_sys_id and a.fst_srvc_dt = b.fst_srvc_dt and a.prov_tin = b.prov_tin and a.mpin = b.mpin),

pl_of_srvc_sum_desc = (select pl_of_srvc_sum_desc from cardiac.pos b

where a.mbr_sys_id=b.mbr_sys_id and a.fst_srvc_dt = b.fst_srvc_dt and a.prov_tin = b.prov_tin and a.mpin = b.mpin)

where a.pl_of_srvc_sys_id is null and pl_of_srvc_sum_desc is null;

run;

It is running now. No row error. The only problem is I changed it yesterday about 4pm and it is now 11am the next day and still running. But, it has to go through 10.1 million rows sooooooooooo Will let you know if this works in the end and if so, great. Thanks.

DBailey
Lapis Lazuli | Level 10

lots of columns/records to update....

Is cardiac.pos sorted or indexed?  If not, then you definitely need to do that.

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 5 replies
  • 743 views
  • 1 like
  • 2 in conversation