topic Re: update 0,4 mln rekords in SAS Programming

update 0,4 mln rekords

Jacek2 — Fri, 21 Oct 2022 11:31:04 GMT

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;

Re: update 0,4 mln rekords

Kurt_Bremser — Fri, 21 Oct 2022 12:13:57 GMT

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.

Re: update 0,4 mln rekords

Jacek2 — Fri, 21 Oct 2022 12:46:31 GMT

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

Re: update 0,4 mln rekords

FreelanceReinh — Fri, 21 Oct 2022 12:48:38 GMT

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;

Re: update 0,4 mln rekords

Jacek2 — Fri, 21 Oct 2022 16:22:00 GMT

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

Re: update 0,4 mln rekords

Jacek2 — Fri, 21 Oct 2022 17:24:39 GMT

What if there is no need to edit?

Re: update 0,4 mln rekords

FreelanceReinh — Fri, 21 Oct 2022 19:02:46 GMT

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.