Proc SQL Update Multiple values

crashz204 — Thu, 11 Jul 2019 16:48:34 GMT

I have a table created in SAS with a list of values. I have a secondary table that I caluclated values on based on table 1.

I am wanting to update table 1 with the calculated values from table 2.

I use the following code.

PROC SQL;
update TEST_MAIN
set (value_1) = (select calc1 from CR_CALC)
where FIELD= 'CR';
RUN;

This updated the VALUE_1 field with the data i need.

however, I have multiple fields to update so I used this example:

PROC SQL;
update TEST_MAIN
set (value_1, value2) = (select calc1. calc2 from CR_CALC)
where FIELD= 'CR';
RUN;

I get this error:

_
22
76
ERROR 22-322: Expecting a name.

ERROR 76-322: Syntax error, statement will be ignored.

I'm not sure what the error is. I used this in SQL syntax all the time without issue.

Thank You!

Re: Proc SQL Update Multiple values

Tom — Thu, 11 Jul 2019 16:54:18 GMT

Not sure what flavor of SQL you are used to using but that syntax is not supported in the SQL standard that PROC SQL uses.

PROC SQL;
update TEST_MAIN 
set var_1= (select calc1 from CR_CALC)
  , var_2 = (select calc2 from CR_CALC)
where FIELD= 'CR'
;
RUN;

Make sure that the subqueries return one and only one value.