BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Linlin
Lapis Lazuli | Level 10

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

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

9 REPLIES 9
Ksharp
Super User

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
Onyx | Level 15

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

Patrick
Opal | Level 21

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
Super User

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
Opal | Level 21

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
Super User

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
Lapis Lazuli | Level 10

Thank you Ksharp! You are always helpful.  - Linlin

Linlin
Lapis Lazuli | Level 10

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
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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