topic Re: fast sort with hash object in SAS Programming

fast sort with hash object

mariopellegrini — Wed, 18 Jan 2023 06:43:58 GMT

Good morning.
I would like to get a lower processing time with the use of hashes than the base sas code for a sort operation.
I have used 2 different hashcodes but the processing times compared to sas base are always higher.
Someone can help me?
Here is my example:

%let righe = 26500000 ;
%let colonne = 10;
data work.tab1 ( keep = keyvar large: ) ;
array keys(1: &colonne) $1 _temporary_;
length keyvar 8;
array largevar [&colonne];
retain largevar 55;
do _i_ = 1 to &righe;
keyvar = ceil (ranuni(1) * &righe);
do j=1 to &colonne;
largevar [j] = floor(round(rand("Uniform")*10,0.01));
end;
output ;
end;
run;

/* sort sas */
proc sort data=tab1 out=ok_sort2;
by keyvar largevar1 largevar2 largevar3 ;
run;

/* hash sort 1 */
data ok_sort1;
if 0 then set tab1;
declare hash sortha(dataset: 'tab1', ordered: 'a', multidata:'y');
declare hiter iter('sortha');
rc=sortha.definekey ("keyvar", "largevar1", "largevar2","largevar3");
rc=sortha.defineData("keyvar", "largevar1", "largevar2","largevar3","largevar4","largevar5","largevar6","largevar7","largevar8","largevar9");
rc=sortha.definedone();
do while ( rc = 0 );
rc = iter.prev(); if rc=0 then output ok_sort1;
end;
stop;
run;

/* hash sort 2 */
data _null_;
if 0 then set tab1;
declare hash sortha(dataset: 'tab1', ordered:'d', multidata:'y');
sortha.definekey ("keyvar", "largevar1", "largevar2","largevar3");
sortha.defineData(all:'yes');
sortha.definedone();
sortha.output(dataset:'ok_sort1');
stop;
run;

Re: fast sort with hash object

PeterClemmensen — Wed, 18 Jan 2023 07:04:29 GMT

The hash object is a great tool for a lot of things. But it is not a replacement for Proc Sort and should not be used for a plain sort operation.

If your only goal is to decrease run-time of your sort, there are options you can try to tweak and see if it improves your result.

For example.

Use the Details Option to get a detailed description of I/O operations of your Proc Sort Step.

If the step above has a lot of I/O steps, increase the Sortsize Option.

Perhaps use the Tagsort Option.

For a detailed list of Proc Sort Options, consult the Proc Sort Documentation.

Re: fast sort with hash object

mariopellegrini — Fri, 20 Jan 2023 17:36:10 GMT

Thanks. so in general the use of hashes is not useful for proc sort?

Re: fast sort with hash object

ballardw — Fri, 20 Jan 2023 18:37:01 GMT

@mariopellegrini wrote:

Thanks. so in general the use of hashes is not useful for proc sort?

If the sole purpose is to sort data then Proc Sort is way more efficient.

If part of the hash is to result in sorting as a side effect of other needed operations then go for it.

Re: fast sort with hash object

mariopellegrini — Mon, 30 Jan 2023 14:52:07 GMT

I didn't find the details option on proc sort, do you mean on proc contents?

Re: fast sort with hash object

Patrick — Mon, 30 Jan 2023 22:14:29 GMT

@mariopellegrini wrote:

I didn't find the details option on proc sort, do you mean on proc contents?

It's all in the documentation.

SORT Procedure

Optimizing Sort Performance