## how to get lead difeerence

Solved
Frequent Contributor
Posts: 84

# how to get lead difeerence

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

Accepted Solutions
Solution
‎03-31-2017 02:23 PM
PROC Star
Posts: 311

## Re: how to get lead difeerence

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.

All Replies
PROC Star
Posts: 311

## Re: how to get lead difeerence

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.

Solution
‎03-31-2017 02:23 PM
PROC Star
Posts: 311

## Re: how to get lead difeerence

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.

Frequent Contributor
Posts: 84

## Re: how to get lead difeerence

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

PROC Star
Posts: 311

## Re: how to get lead difeerence

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.

☑ This topic is solved.