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;
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.
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.
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.