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 |
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.
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.
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.
This is taking too much time if the dataset big becuase of cross join..is there any other solution?
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.