0

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.

Ian
  • 35
  • 1
  • 4
  • Please show what code you have tried. Sounds like it could be approached using a look-back or a look-ahead. – Quentin Dec 20 '18 at 10:21

1 Answers1

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
Tom
  • 47,574
  • 2
  • 16
  • 29
  • That does it! After posting this question and puzzling on it a bit more, I approached it from a different angle. I created a separate version of the original table with rank-1 and joined against the original to create the paired set. Your version is much more elegant. Thanks! – Ian Dec 20 '18 at 14:29
  • You can use the -1 trick without making a second copy if you use it in PROC SQL to join the table with itself. `on a.code = b.code and a.rank = b.rank+1` – Tom Dec 20 '18 at 14:44