topic Re: Updating column of one table with another in SAS Programming

Updating column of one table with another

mauri0623 — Tue, 08 Oct 2019 15:10:47 GMT

good morning,

Either sql or data step is fine with me.

I am trying to update the obligating_doc_no rom pips_data with obligating_document_no from B7600 table. I get an error indicating that "

ERROR: Subquery evaluated to more than one row.

NOTE: Correlation values are: IPAC='89000001' ." I used the code below.

proc sql;

update pips_data p

set obligating_doc_no =

(select obligating_doc_no

from tableB b

where p.ipac = b.ipac

)

where obligating_doc_no in

(select obligating_doc_no

from tableB

);

quit;

Re: Updating column of one table with another

Reeza — Tue, 08 Oct 2019 15:25:14 GMT

Well, what happens when multiple rows meet the condition? Is there a date field you need to be using to limit it? Why do you have duplicates?

Re: Updating column of one table with another

mauri0623 — Tue, 08 Oct 2019 17:42:14 GMT

That is how the data is. No date.

Re: Updating column of one table with another

Reeza — Tue, 08 Oct 2019 17:46:29 GMT

I cannot download attachments so have no idea about your data unless you include it directly in the post. Regardless, the SAS error is telling you have duplicates returned. In that situation, which value do you want to use? You need to make a, usually, business/subject matter expertise decision to determine which record is required. If you have exact duplicates for some reason, you can either first remove duplicates or you can use MAX()/MIN() on the character variables.

Re: Updating column of one table with another

ballardw — Tue, 08 Oct 2019 19:33:37 GMT

It is pretty easy to see that you have duplicate values for OBLIGATING_DOC_NO with the same IPAC value. This is from the first 3 data lines of your B7600s.csv, which I have to assume is related to your TableB.

IPAC	OBLIGATING_DOC_NO	TAS	ACCOUNTING
89000001	89233119SNA000165	08920192020 0251000	01278.2019.01.100260.2510
89000001	89233119SNA000165	089 X0251000	01250.2019.01.100260.2510
89000001	89233119SNA000165	08920192020 0240000	70052.2019.60.300602.2510

Subqueries used like the highlighted need to return a single value

proc sql;
update pips_data p

set obligating_doc_no =
(select obligating_doc_no
from tableB b
where p.ipac = b.ipac
)

like the error message says. So you need an additional filter on your data to ensure you get one and only one value of the variable from the subquery for the value of Ipac.