topic Hash Sort Ascending and Descending in SAS Programming

Hash Sort Ascending and Descending

Mushy — Tue, 17 May 2022 11:11:08 GMT

Hello,

Do we have an equivalent hash code for :

proc sort data=test;by id descending sub;
run;

I would like to see how we could define multiple keys ordered mixed.

Thanks,

Mushy

Re: Hash Sort Ascending and Descending

PeterClemmensen — Tue, 17 May 2022 11:51:23 GMT

No unfortunately it's not possible. For multiple keys, they will either be all sorted ascending - or descending when you use the Ordered : Argument Tag.

Re: Hash Sort Ascending and Descending

mkeintz — Tue, 17 May 2022 15:22:56 GMT

There's no direct analog to "by x y descending z" in hash object storage.

But you can replicate it by use of hash-of-hashes. Here's a made-up example that replicates BY MAKE DESCENDING WEIGHT for sashelp.cars:

proc sort data=sashelp.cars out=cars;
  by descending make;
run;

data want (drop=_:);
  if 0 then set cars;
  declare hash h ;
  declare hiter hi ;
  declare hash hoh (ordered:'A');
    hoh.definekey('make');
    hoh.definedata('make','h','hi');
    hoh.definedone();
  declare hiter hohi ('hoh');


  do until (end_of_cars);
    set cars end=end_of_cars;
    if hoh.find() ^=0 then do;
      h=_new_ hash(dataset:'cars (obs=0)',ordered:'D',multidata:'Y');
      h.definekey('weight');
      h.definedata(all:'Y');
      h.definedone();
      hi=_new_ hiter('h');
      hoh.add();
    end;
    h.add();
  end;

  do _i=hohi.first() by 0 until (hohi.next()^=0);
    do _j=hi.first() by 0 until (hi.next()^=0);
      output;
    end;
  end;
run;

In the location of the "OUTPUT" statement is where you might also do programming that depends on the reordered sequence. For instance if you wanted change of weight from one obs to the next:

  call missing(of _all_);
  do _i=hohi.first() by 0 until (hohi.next()^=0);
    do _j=hi.first() by 0 until (hi.next()^=0);
      weight_change=dif(weight);
      if make^=lag(make) then weight_change=.;
      output;
    end;
  end;

I preceded this with the CALL MISSING(of _ALL_), which wasn't essential in this particular example, but is a good idea for properly replicating reading the first record in a SET .... BY MAKE DESCENDING WEIGHT.

This could be definitely by greatly simplified by a sas implementation of something like "ORDERED:(MAKE descending WEIGHT)" - there would be no need for coding a hash-of-hashes solution.

Re: Hash Sort Ascending and Descending

FreelanceReinh — Tue, 17 May 2022 17:47:41 GMT

Hello @Mushy,

Alternatively you can create one or more replacement keys whose sort order reverses the sort order of the corresponding original keys. For example, use -sub for a numeric variable sub or

-input(put(sub,$hex6.),hex6.)

for a character variable sub of length 3. (For character variables of arbitrary lengths you could replace each character c by byte(255-rank(c)).)

Example using mkeintz's dataset CARS:

proc sql noprint;
select quote(trim(name)) into :varlist separated by ','
from dictionary.columns
where libname='WORK' & memname='CARS';
quit;

data _null_;
if _n_=1 then do;
  dcl hash h(ordered:'a', multidata:'y');
  h.definekey('make','_w');
  h.definedata(&varlist);
  h.definedone();
end;
set cars end=last;
_w=-weight;
h.add();
if last then h.output(dataset:'want');
run;