Hash Sort Ascending and Descending

[Mushy]

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

[PeterClemmensen]

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.

[mkeintz]

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.

[FreelanceReinh]

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;