how to get lead difeerence

[kumarK]

Hi Guys, is tehre a direct funtion to get the difference like LAG function?

or how can we achive the difference column results? Thanks

 have dataset:

Date	Value
06/12/2013	250
07/12/2013	200
08/12/2013	160
09/12/2013	130
10/12/2013	120

want dataset:

Date	Value	Difference
06/12/2013	250	50
07/12/2013	200	40
08/12/2013	160	30
09/12/2013	130	10
10/12/2013	120	0

[collinelliot]

Is there a direct function that does a look-ahead like the lag does a look-behind? Not that I know of. But you can find plenty of examples here of how to do this kind of a look-ahead calculation if that would suffice.

[collinelliot · Accepted solution]

For example, the following would generate the results for your example:

data have;
    format date date9.;
    input date mmddyy10. value;
datalines;
06/12/2013 250
07/12/2013 200
08/12/2013 160
09/12/2013 130
10/12/2013 120
;

data want(drop = value2);
    merge have
          have(firstobs = 2 drop = date rename=(value = value2));
    diff = coalesce(value - value2, 0);
run;

If your actual data have by groups or some other complicating factor, you will have to adjust the code accordingly.

[kumarK]

This is taking too much time if the dataset big becuase of cross join..is there any other solution?

[collinelliot]

In your actual data, if there are a lot of variables you'll want to make sure that you only keep the "value2" that's used for the calculation. If you have a lot of other variables, it will not only mess up the other variables, but will slow processing. Other solutions that are less processing intensive? I don't know. There's a lot of way smarter people on here, so maybe they'll chime in.