Search code examples
loopsdatasetsasindices

SAS combining datasets, binary search, indices


In SAS, for the two test datasets below - for every value of "amount" that falls within "y" and "z", I need to extract the corresponding "x". There could be multiple values of "x" that fit into the criteria. The final result should look something like this:

/*
4 banana eggs
15 .
31 .
7 banana
22 fig
1 eggs
11 coconut
17 date
41 apple
*/

I realize this relies on using indices or binary searches but I can't figure out a workable solution! Any help would appreciated! Thanks!

data test1;
   input x $ y z;
   datalines;
   apple 29 43
   banana 2 7
   coconut 9 13
   date 17 20
   eggs 1 5
   fig 18 26
   ;
run;

data test2;
   input amount;
   datalines;
   4
   15
   31
   7
   22
   1
   11
   17
   41
   ;
run;

Solution

  • Join the two datasets so amount falls between y and z.

    proc sql;
    create table join as 
    select a.amount
          ,b.*
    from test2 a
           left join
         test1 b
           on a.amount between b.y and b.z;
    quit;
    

    Sort the result by amount for transpose.

    proc sort data=join; by amount; run;
    

    Transpose it.

    proc transpose data=join out=trans;
    by amount;
    var x;
    run;
    

    Now you have your fruits each in its own variable named col1, col2, .... If you want them all in one variable separated by a blank, just concatenate them.

    data trans2(keep= amount text);
    set trans(drop=_name_);
    array v{*} _character_;
    text = catx(' ', of v{*});
    run;