Hash Objects Find Greater than

[Mushy]

Hello All,

In hash programming is there a way to find/lookup for keys which has greater than or less than the hashkey, instead of equal to?

For example: in the below code output we have the final dataset created id roll=2. I would like to know if there is a way to create the final dataset with data where roll>2 or roll<2 ?

data one;
input roll ;
cards;
1
2
4
6
;
run;

data final;
if _n_=1 then do;
declare hash aa();
aa.definekey('roll');
aa.definedata('dat');
aa.definedone();
roll=2;
dat="Match";
aa.add();
end;
set one;
if aa.find() =0 then output;
run;

Thanks,

Mushy

[KachiM]

It can be done but it is an unusual requirement with hash objects.

You set the variable 'dat' to missing and replace the statement

if aa.find() =0 then output;

as 

if aa.find() NE 0 then output;

[Mushy]

Hello Datasp,

Thanks for the response!

the solution provided by you:

if aa.find() NE 0 then output;

will produce output table with records where the key is not matching ie. all records which doesnt have roll=2, but i need all those greater that roll=2. 

Thanks,

Mushy

[KachiM]

In your original post you wanted roll < 2 and roll > 2. 

If your choice is to get one output data set when roll > 2 OR if you want it for roll < 2 here is one way:

%let choice = >;
data final;
if _n_=1 then do;
declare hash aa();
aa.definekey('roll');
aa.definedata('dat');
aa.definedone();
roll=2;
dat="Match";
aa.add();
p_roll = roll;
drop p_roll;
end;
call missing(dat);
set one;
if roll &choice. p_roll and 
   aa.find() ne 0 then output;
run;

If you want for roll < 2, change the macro variable into "<"

If you want for roll < 2 and roll > 2 in one data step here is a way:

data lower upper;
if _n_=1 then do;
declare hash aa();
aa.definekey('roll');
aa.definedata('dat');
aa.definedone();
roll=2;
dat="Match";
aa.add();
p_roll = roll;
drop p_roll;
end;
call missing(dat);
set one;
if roll > p_roll and 
   aa.find() ne 0 then output upper;
else if roll < p_roll and
   aa.find() ne 0 then output lower;
run;

[andreas_lds]

In hash programming is there a way to find/lookup for keys which has greater than or less than the hashkey, instead of equal to?

No, this is not possible. At least not without using a loop to query the hash object multiple times.

Can you describe your task in more details? Most likely someone will suggest something helpful.

[Mushy]

Hello Andreas,

Thanks for responding!

Indeed, the intention is to try to replace sql queries with selection criteria with different operators.

Thanks

Mushy

[Kurt_Bremser]

You could use a hash, and even better, a temporary array, if the space of valid keys is known beforehand. In your case, it is the numbers on dice, so 1 to 6.

data want1;
set have;
array lookup {1:6} $ _temporary_ ('low','match','greater','greater','greater','greater');
match = lookup{roll};
run;

data want2;
if 0 then set have;
if _n_ = 1
then do;
  declare hash lookup ();
  lookup.definekey("roll");
  lookup.definedata("next_lower","next_greater");
  lookup.definedone();
  do roll = 1 to 6;
    if roll lt 2
    then do;
      next_lower = 0;
      next_greater = 2;
    end;
    else if roll = 2
    then do;
      next_lower = 2;
      next_greater = 2;
    end;
    else do;
      next_lower = 2;
      next_greater = 99;
    end;
    rc = lookup.add();
  end;
end;
set have;
rc = lookup.find();
drop rc;
run;

You can find two variables (like I did in the second step) by using two arrays, and you can, of course, use only one target variable in the hash.

[mkeintz]

Use the "ordered" feature of hash objects, plus a hash iterator.

I understand your goal to be to find all the keys greater than some test value.  Here are 3 test values 2,5, and 7, with a report of all ROLL values greater than the test:

data one;
input roll @@;
cards;
1  2  4  6
run;

data want;
  if 0 then set one;
  declare hash h (dataset:'one',ordered:'a');
    h.definekey('roll');
    h.definedata('roll');
    h.definedone();
  declare hiter hi ('h');

  do test=2,5,7;
    put test=;
    do rc=hi.last() by 0 while (roll>test and rc=0);
      put +3 roll= ;
      rc=hi.prev();
    end;
  end;
run;

[mkeintz]

My program showed the tools you need, but I am uncertain as to what you want the output to look like.  Show that to get a better response.

[Mushy]

Thanks mkeintz and Kurt for helping me out with possible solutions. The solution I was looking for was an equivalent of an sql statement with a where condition > or < .

Thanks

Mushy

[Kurt_Bremser]

You may want to support my recent Ballot Idea:

Provide Sequential Search Capability for Hash Objects