topic Re: Can I order one variable in Ascending and another in descending order in Hash? in SAS Programming

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

Linlin — Thu, 05 Apr 2012 19:28:55 GMT

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

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

Ksharp — Fri, 06 Apr 2012 01:48:44 GMT

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

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

Haikuo — Fri, 06 Apr 2012 02:33:47 GMT

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

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

Linlin — Fri, 06 Apr 2012 03:46:52 GMT

Thank you Ksharp! You are always helpful. - Linlin

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

Patrick — Sun, 08 Apr 2012 01:31:34 GMT

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;

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

art297 — Sun, 08 Apr 2012 02:03:32 GMT

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.

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

Linlin — Sun, 08 Apr 2012 11:33:12 GMT

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;

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

Ksharp — Mon, 09 Apr 2012 04:20:20 GMT

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

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

Ksharp — Mon, 09 Apr 2012 04:48:08 GMT

Patrick,

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

I have an alternative way.

Ksharp

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

Ksharp — Mon, 09 Apr 2012 05:18:04 GMT

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