BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ruser
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

3 REPLIES 3
Astounding
PROC Star

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.

ruser
Calcite | Level 5
This is great! My solutions were much longer (and didn't work exactly as I wanted). Thanks.
Jagadishkatam
Amethyst | Level 16

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

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.

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