topic Re: Hash Sort in SAS Programming

Hash Sort

JackoNewbie — Wed, 10 Feb 2021 16:07:53 GMT

Hi,

I am new to hash sas programming. I have found some useful code which will split my dataset in to different tables depending on a certain variable. I am looking to include a sort to the code for one of the columns in each table but struggling to see how this would fit in with the hash code. I know I can use a macro variable at the end then a proc sort but would rather have it included in the hash code.

Outcome: Datasets one, two and three to be sorted in descending order on 'var1'.

Any help at all would be appreciated.

data have;
	input ID $ var1 var2;
datalines;
one 100 200
one 100 200
three 300 400
three 500 200
three 200 100
two 400 200
two 300 166
one 300 100
three 400 400
;

proc sort data=have;by id;run;
data _null_;
 if _n_=1 then do;
   if 0 then set have;
   declare hash h(dataset:'have(obs=0)',multidata:'y');
   h.definekey(all:'y');
   h.definedata(all:'y');
   h.definedone();
 end;
do until(last.id);
 set have;
 by id;
 h.add();
end;
h.output(dataset:id);
h.clear();
run;

Re: Hash Sort

PeterClemmensen — Wed, 10 Feb 2021 16:31:42 GMT

Simple specify the variable of interested only in the definekey and use the ordered:’y’ tag in the declare Statement

Re: Hash Sort

FreelanceReinh — Wed, 10 Feb 2021 16:39:01 GMT

Hi @JackoNewbie,

You can add descending var1 to the BY statement of the PROC SORT step and then use

h.definekey('id');

or leave the PROC SORT step unchanged, insert the ordered:'d' argument tag in the DECLARE statement and use

h.definekey('var1');

The "if 0 then set have;" is redundant because of the other SET statement.

Re: Hash Sort

JackoNewbie — Wed, 10 Feb 2021 17:25:57 GMT

Thanks

Re: Hash Sort

JackoNewbie — Wed, 10 Feb 2021 17:26:50 GMT

First option worked. Thanks for the solution.

Re: Hash Sort

PeterClemmensen — Wed, 10 Feb 2021 19:02:47 GMT

If you're interested, you can do the while split thing with no pre sorting using hash of hashes like this. Your data can't be too big though.

data have;
	input ID $ var1 var2;
datalines;
one 100 200
one 100 200
three 300 400
three 500 200
three 200 100
two 400 200
two 300 166
one 300 100
three 400 400
;

data _null_;
   dcl hash hh();
   hh.definekey("id");
   hh.definedata("h", "id");
   hh.definedone();
   dcl hiter i("hh");

   do until (z);
      set have end = z;
      if hh.find() ne 0 then do;
         dcl hash h(dataset : "have(obs = 0)", multidata : "Y", ordered : "Y");
         h.definekey("var1");
         h.definedata(all : "Y");
         h.definedone();
         hh.add();
      end;
      h.add();
   end;

   do while (i.next() = 0);
      h.output(dataset : id);
   end;
run;