Transform Ordered Values to Paired

Question

I'm looking to transform a set of ordered values into a new dataset containing all ordered combinations.

For example, if I have a dataset that looks like this:

Code      Rank     Value     Pctile
1250      1        25        0
1250      2        32        0.25
1250      3        37        0.5
1250      4        51        0.75
1250      5        59        1

I'd like to transform it to something like this, with values for rank 1 and 2 in a single row, values for 2 and 3 in the next, and so forth:

Code      Min_value     Min_pctile     Max_value     Max_pctile
1250      25            0              32             0.25
1250      32            0.25           37             0.5
1250      37            0.5            51             0.75
1250      51            0.75           59             1

It's simple enough to do with a handful of values, but when the number of "Code" families is large (as is mine), I'm looking for a more efficient approach. I imagine there's a straightforward way to do this with a data step, but it escapes me.

Answer 1

Looks like you just want to use the lag() function.

data want ;
  set have ;
  by code rank ;
  min_value = lag(value) ;
  min_pctile = lag(pctile) ;
  rename value=max_value pctile=max_pctile ;
  if not first.code ;
run;

Results

                        max_     max_      min_     min_
Obs    Code    Rank    value    pctile    value    pctile

 1     1250      2       32      0.25       25      0.00
 2     1250      3       37      0.50       32      0.25
 3     1250      4       51      0.75       37      0.50
 4     1250      5       59      1.00       51      0.75