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!
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.
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.