Help using Base SAS procedures

Update Query Error

Reply
Regular Contributor
Regular Contributor
Posts: 238

Update Query Error

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;

Super Contributor
Posts: 578

Re: Update Query Error

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?

Regular Contributor
Regular Contributor
Posts: 238

Re: Update Query Error

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.

Super Contributor
Posts: 578

Re: Update Query Error

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

Regular Contributor
Regular Contributor
Posts: 238

Re: Update Query Error

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.

Super Contributor
Posts: 578

Re: Update Query Error

lots of columns/records to update....

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

Ask a Question
Discussion stats
  • 5 replies
  • 202 views
  • 1 like
  • 2 in conversation