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

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.

View solution in original post


All Replies
PROC Star
Posts: 288

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

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

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
  • 139 views
  • 2 likes
  • 2 in conversation