topic Re: Update Query Error in SAS Procedures

Update Query Error

tmm — Thu, 20 Jun 2013 14:17:49 GMT

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;

Re: Update Query Error

DBailey — Thu, 20 Jun 2013 14:53:31 GMT

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?

Re: Update Query Error

tmm — Thu, 20 Jun 2013 16:07:09 GMT

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.

Re: Update Query Error

DBailey — Fri, 21 Jun 2013 13:00:17 GMT

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

Re: Update Query Error

tmm — Fri, 21 Jun 2013 15:09:04 GMT

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.

Re: Update Query Error

DBailey — Mon, 24 Jun 2013 12:31:59 GMT

lots of columns/records to update....

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