DATA Step, Macro, Functions and more

how to get lead difeerence

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 84
Accepted Solution

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: 307

Re: how to get lead difeerence

Posted in reply to collinelliot

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.

View solution in original post


All Replies
PROC Star
Posts: 307

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: 307

Re: how to get lead difeerence

Posted in reply to collinelliot

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

Posted in reply to collinelliot

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

PROC Star
Posts: 307

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.

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

Discussion stats
  • 4 replies
  • 143 views
  • 2 likes
  • 2 in conversation