DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Super Contributor
Posts: 1,636
Accepted Solution

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

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


Accepted Solutions
Solution
‎04-05-2012 09:48 PM
Super User
Posts: 10,035

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

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

View solution in original post


All Replies
Solution
‎04-05-2012 09:48 PM
Super User
Posts: 10,035

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

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

Respected Advisor
Posts: 3,156

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

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

Respected Advisor
Posts: 4,173

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

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;

Super User
Posts: 10,035

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

Patrick,

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

I have an alternative way.

Ksharp

PROC Star
Posts: 7,474

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

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.

Super User
Posts: 10,035

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

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

Super Contributor
Posts: 1,636

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

Thank you Ksharp! You are always helpful.  - Linlin

Super Contributor
Posts: 1,636

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

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;

Super User
Posts: 10,035

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

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 539 views
  • 6 likes
  • 5 in conversation