topic Re: Issue in using CALCULATED VARIABLE in proc update SET statement in SAS Programming

Issue in using CALCULATED VARIABLE in proc update SET statement

PrakashBashyam — Thu, 30 Apr 2015 16:29:25 GMT

I m using below query

proc sql;

update Work.Sim_re_BERM

set L9BE_C=max(0,L9_C*(L9BE/L9)),

L9RM_C=L9_C - calculated L9BE_C;

Quit;

This is throwing an ERROR: The illegal use of a CALCULATED variable has been detected in an UPDATE statement.

Re: Issue in using CALCULATED VARIABLE in proc update SET statement

PGStats — Thu, 30 Apr 2015 16:58:01 GMT

That query makes no sense to me. What are you trying to do? - PG

Re: Issue in using CALCULATED VARIABLE in proc update SET statement

PrakashBashyam — Thu, 30 Apr 2015 17:30:40 GMT

Sorry, I am not getting your questions

L_* are columns of the table Work.Sim_re_BERM.

Setting L9BE_C based on calculation max(0,L9_C*(L9BE/L9))

And L9RM_C based difference between L9_C on CALCUALTED L9BE_C.

Re: Issue in using CALCULATED VARIABLE in proc update SET statement

PGStats — Thu, 30 Apr 2015 18:08:43 GMT

OK, maybe I get it now. Have you tried getting rid of CALCULATED :

proc sql;

update Work.Sim_re_BERM

set

L9BE_C = max(0, L9_C*(L9BE/L9)),

L9RM_C = L9_C - max(0, L9_C*(L9BE/L9));

Quit;

Re: Issue in using CALCULATED VARIABLE in proc update SET statement

PrakashBashyam — Thu, 30 Apr 2015 18:32:30 GMT

it perfectly works. But would like to understand whats wrong in using CALCUALTED variable like that

Re: Issue in using CALCULATED VARIABLE in proc update SET statement

PGStats — Thu, 30 Apr 2015 18:59:01 GMT

SQL is a formal language with very strict syntax. That is very different from natural languages which are much more flexible but also more prone to misinterpretation. I think the UPDATE query is made such that every assignment (L9BE_C =, L9RM_C =, etc) clause can be treated separately (and thus, in parallel).