Can I order one variable in Ascending and another in descending order in Hash?

[Linlin]

Hi,

I like to have the output dataset first ordered by ascending Id then by descending var. Is it possible in hash?

Thanks - Linlin

data have;

input id  var @@;

cards;

1 2 1 3 10 6 8 5 10 7 8 2

;

data _null_;

  set have (obs=1);

dcl hash h( dataset: 'have', ordered: 'Ascending',multidata:'yes');

h.definekey('id','var');

h.definedata(all:'y');

h.definedone();

h.output(dataset:'want');

run;

I want the output like below:

                        Obs    id    var

                         1      1     3

                         2      1     2

                         3      8     5

                         4      8     2

                         5     10     7

                         6     10     6

[Ksharp · Accepted solution]

It  almost cann't. But there is a  workaround way.

data have;
input id  var @@;
cards;
1 2 1 3 10 6 8 5 10 7 8 2
;
run;

data _null_;
declare hash h(ordered: 'Ascending',multidata:'yes');
    h.definekey('id','_var');
    h.definedata('id','var');
    h.definedone();
do until(last);
 set have end=last;
 _var=-1*var;
 h.add();
end;
    h.output(dataset:'want');
run;

Ksharp

[Haikuo]

Ksharp, very slick! though I am wondering if there is a way you can 'walk around' upon character variables.

[Patrick]

Using Ksharp's approach one could convert a string to its octal representation and use this value for sorting.

The code example below uses only the first five characters for sorting (as the octal representation ends up as a 15 digit number). I believe this is most of the time sufficient.

data have;
input id  var:$9. @@;
cards;
1 ab 1 xyz 10 D9a 8 test 10 Xyz 8 9This
;
run;

data _null_;
  declare hash h(ordered: 'Ascending',multidata:'yes');
      h.definekey('id','_var');
      h.definedata('id','var');
      h.definedone();
  do until(last);
   set have end=last;
   _var=-1*input(put(subpad(var,1,5),octal.),15.);
   h.add();
  end;
  h.output(dataset:'want');
run;

[Ksharp]

Patrick,

As always, I am amazing with your idea . But unfortunately you only process the first five character.

I have an alternative way.

Ksharp

[art297]

Linlin: I had suggested using the rank function, but I'm retracting that suggestion as it would only work for one character strings.  It worked for your example because the first character of the string was the only one that was needed to differentiate (correctly) order the strings.

[Ksharp]

Art,

I think Rank() can only handle one character ,not for a string, so it is not a good idea.

E.X.(wrong result)

data have;
input id  var:$9. @@;
cards;
1 aa 1 ayz 10 Xaa 8 test 10 Xyz 8 This
;
run;

data _null_;
  declare hash h(ordered: 'Ascending',multidata:'yes');
      h.definekey('id','_var');
      h.definedata('id','var');
      h.definedone();
  do until(last);
   set have end=last;
   _var=-1*rank(var);
   h.add();
  end;
  h.output(dataset:'want');
run;

Ksharp

[Linlin]

Thank you Ksharp! You are always helpful.  - Linlin

[Linlin]

Thank you Patrick and Art! Both methods worked great!  - Linlin

Art's method:

data have;

input id  var:$9. @@;

cards;

1 ab 1 xyz 10 D9a 8 test 10 Xyz 8 9This

;

run;

data _null_;

  declare hash h(ordered: 'Ascending',multidata:'yes');

      h.definekey('id','_var');

      h.definedata('id','var');

      h.definedone();

  do until(last);

   set have end=last;

   _var=-1*rank(var);

   h.add();

  end;

  h.output(dataset:'want');

run;

proc print;run;

[Ksharp]

Here is. But I don't like it yet.

data have;
input id  var & $9. @@;
cards;
1 a bc  1 a cb   10 D9a   8 test   10 Xyz    8 9This
;
run;

data _null_;
if 0 then set have;
  declare hash h1(dataset:'have',ordered: 'Ascending');
  declare hiter hi1('h1');
      h1.definekey('id');
      h1.definedata('id');
      h1.definedone();
if 0 then set have(rename=(id=_id));
  declare hash h2(dataset:'have(rename=(id=_id))',ordered:'D',multidata:'Y');
  declare hiter hi2('h2');
      h2.definekey('_id','var');
      h2.definedata('_id','var');
      h2.definedone();
  declare hash h3(ordered:'A');
      h3.definekey('count');
      h3.definedata('id','var');
      h3.definedone();

do while(hi1.next()=0);
 do while(hi2.next()=0);
  if id=_id then do;count+1;h3.add(); end;
 end;
end;
h3.output(dataset:'want');
run;

Ksharp