Equivalent in SQL of Update statement

elsfy — Wed, 16 Mar 2022 10:22:38 GMT

I have this code :

data want ; update have result ; /* updates table have with table result values */ 
by var ; 
run ;

i would like to know the equivalent way to do this with proc SQL in order to avoid the proc sort step before the data step.

Re: Equivalent in SQL of Update statement

PaigeMiller — Wed, 16 Mar 2022 10:27:24 GMT

Surely you realize that SQL still has to do a sort in order to do the update. You just don't have to program it in SAS.

Re: Equivalent in SQL of Update statement

mkeintz — Wed, 16 Mar 2022 15:09:19 GMT

I agree with @PaigeMiller that SQL needs to do a sort under the hood. It may be a bit more efficient than PROC SORT because the sorting only generates utility files (or maybe even just memory objects) rather than making a complete SAS dataset file.

But consider the far more wordy code needed to replicate the UPDATE statement, which only replaces a value from HAVE with a value from RESULT when the new value is not missing. As far as I know, this means coding explicit COALESCE (or COALESCEC) functions for each variable that is present in both datasets. For example, when you have a one-to-one join, you would need to do something like this:

data HAVE;
input id  a b c;
datalines;
1  11 111 1111
2  .  222 2222
3  33  .  3333
run;
data RESULT;
input id a b c ;
datalines;
1 -11 -111 -1111 
2 -22 -222 -2222
3  .  -333 -3333
run;

proc sql;
  create table ljoin as
  select coalesce(h.a,r.a) as a, coalesce(h.b,r.b) as b, coalesce(h.c,r.c) as c
  from HAVE as h left join RESULT as r on h.id=r.id;
quit;

And the above wouldn't replicate UPDATE for a one-to-many join (UPDATE would generate only 1 record per by-group). I leave it to those more expert in SQL to show how that would be done.

The point is there is far more coding required to avoid a preliminary PROC SORT when replicating UPDATE. I'm not even sure it can be done, because the UPDATE statement depends on the within-bygroup order of incoming RESULT records. SQL makes no commitment to honor such record order.

topic Equivalent in SQL of Update statement in SAS Programming

Equivalent in SQL of Update statement

Re: Equivalent in SQL of Update statement

Re: Equivalent in SQL of Update statement