update 0,4 mln rekords

[Jacek2]

Hi
I have table wsad with 1,6 mln obs and table limit with 0,4 mln obs.
the code below works with several hundred records, but freezes on more
What to do to make it work with more records?

proc sql; 
update WSAD as a 
set pkt = (select pkt from limit b where a.id= b.id),
     data = (select data from limit b where a.id = b.id)
 where a.id in (select id from limit);
 quit;

[Kurt_Bremser · Accepted solution]

From your code, I guess that id is unique in both datasets.

This should be much faster:

proc sort data=wsad;
by id;
run;

proc sort data=limit;
by id;
run;

data want;
update
  wsad
  limit (keep=id pkt data)
;
by id;
run;

PS sub-selects are notoriously slow in SAS SQL.

[Jacek2]

Thank you Kurt, it works great and very fast !!!

[FreelanceReinh]

Hi @Jacek2,

If there is a requirement to edit dataset WSAD in place (which is what your original code does) and you want to avoid sorting, you can use the MODIFY statement of the DATA step together with a hash object:

data wsad;
if _n_=1 then do;
  dcl hash h(dataset:'limit');
  h.definekey('id');
  h.definedata('pkt','data');
  h.definedone();
end;
modify wsad;
if h.find()=0;
run;

[Jacek2]

Thanks for the answer, it's always nice to get to know something new.

[Jacek2]

What if there is no need to edit?

[FreelanceReinh]

If you don't mind overwriting WSAD with the updated version, but you still want to avoid sorting, you can replace these two lines

modify wsad;
if h.find()=0;

by these three

set wsad;
rc=h.find();
drop rc;

in my code.