topic Re: code optimization with hashes in SAS Programming

code optimization with hashes

mariopellegrini — Tue, 28 Feb 2023 14:44:27 GMT

Good morning everyone. I'm trying to optimize some time consuming code, I'm asking for suggestions. Starting from a typical situation that I report in the example code below, it is a matter of identifying through sort + data step with the use of by of the records, I was wondering if with hash techniques there is the possibility of improving the processing time

data ds_1;
input cod1 cod2;
datalines;
1 4
1 12
1 7
2 7
2 6
2 9
3 12
3 4
;
proc sort data=ds_1;
by cod1 cod2;
run;

data ds_2;
set ds_1;
by cod1 cod2;
if last.cod1;
run;

Re: code optimization with hashes

Kurt_Bremser — Tue, 28 Feb 2023 16:06:06 GMT

How large is your real dataset (number of observations, observation size)?

How long do your steps take in real life?

Re: code optimization with hashes

mariopellegrini — Tue, 28 Feb 2023 17:50:42 GMT

35,800,340 observations and 11 variables in total (8 variables in the "by")
The original date step lasts:
real time 5:38.72
cpu time 5:10.59

Re: code optimization with hashes

Kurt_Bremser — Tue, 28 Feb 2023 18:50:49 GMT

If your initial dataset is already sorted by cod1, you could avoid the PROC SORT by using a DOW loop:

data want;
do until (last.cod1);
  set have;
  by cod1;
  _cod2 = max(_cod2,cod2);
end;
do until (last.cod1);
  set have;
  by cod1;
  if cod2 = _cod2 then output;
end;
drop _cod2;
run;