SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Creating intervals from adjacent observations

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Creating intervals from adjacent observations

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!


Accepted Solutions
Solution
‎01-05-2017 07:45 PM
Super User
Posts: 5,081

Re: Creating intervals from adjacent observations

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.

View solution in original post


All Replies
Solution
‎01-05-2017 07:45 PM
Super User
Posts: 5,081

Re: Creating intervals from adjacent observations

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.

New Contributor
Posts: 2

Re: Creating intervals from adjacent observations

This is great! My solutions were much longer (and didn't work exactly as I wanted). Thanks.
Trusted Advisor
Posts: 1,128

Re: Creating intervals from adjacent observations

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;
Thanks,
Jag
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 282 views
  • 0 likes
  • 3 in conversation