topic Re: Creating intervals from adjacent observations in SAS Data Management

Creating intervals from adjacent observations

ruser — Thu, 05 Jan 2017 18:37:51 GMT

Hi, I'm looking for an efficient way to transform data describing a step function from a format where observations give the locations of "jumps" to a format that has intervals where the step function is constant. I want to define step functions to start at 0 with a value of 0 (unless a different value is specified in the input dataset). I usually have success searching SAS forums using Google or the like, but unfortunately not this time! I'm using SAS V9.4.

For example, let's say I have data like the following:

data test;
  input id x y;
  cards;
  1 0 20
  1 5 15
  1 10 17
  2 1 5
  2 10 2
  3 7 8
;
run;

Here id is identifies a particular step function (there could be more than one defined in a particular dataset), x is the location of a jump and y is the value the step function takes on at that jump (technically, I guess I'm imagining that this function is right-continuous). So, function 1 starts at 0 with a value of 20, is constant until x=5 when it jumps to a value of 15, stays constant until x=10 when it assumes a value of 17 and is constant thereafter.

I'd like to end up with a dataset like:

data test2;
  input id xstart xstop y;
  cards;
  1 0 5 20
  1 5 10 15
  1 10 . 17
  2 0 1 0
  2 1 10 5
  2 10 . 2
  3 0 7 0
  3 7 . 8
;
run;

Where id is the same id as in the previous dataset, but xstart to xstop defines intervals where the step function is constant and y is the value of the function during a particular interval.

The solutions I've tried become very complex and I'd be happy for any pointers towards efficient solutions using either a data step or PROC SQL (or both).

Thanks!

Re: Creating intervals from adjacent observations

Astounding — Thu, 05 Jan 2017 20:07:00 GMT

Here's a DATA step approach. It might be complex, but at least it's short:

data want;

set test (rename=(x=xstart));

by id;

set test (firstobs=2 keep=x rename=(x=xstop)) test (drop=_all_);

if last.id then xstop=.;

run;

It does the heavy lifting (getting XSTART and XSTOP), but not all of the work. It doesn't insert the 0 steps.

Re: Creating intervals from adjacent observations

ruser — Fri, 06 Jan 2017 00:46:24 GMT

This is great! My solutions were much longer (and didn't work exactly as I wanted). Thanks.

Re: Creating intervals from adjacent observations

Jagadishkatam — Fri, 06 Jan 2017 01:43:04 GMT

another approach

data want2;
num=_n_+1;
set test ;
if nobs>=num then set test(keep=id x rename=(id=id2 x=xstop)) nobs=nobs point=num;
if id^=id2 then call missing(xstop);
drop id2;
run;