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;
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?
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.
How would you determine which of the multiple rows to use for the update?
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.
lots of columns/records to update....
Is cardiac.pos sorted or indexed? If not, then you definitely need to do that.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.