10-05-2017 03:43 PM
I have the following data set which i need to extend and fill values by looking at the previous values.
data have; input date type $ gp1 $ grp2 $ amnt; datalines; 2001 . . . 0.002 2001 MRA TL08 T630 0.0004 2001 MRA TB08 T630 0.0005 2003 . . . 0.001 2003 MRA TB08 T630 0.007 2005 . . . 0.0003 ; run;
For the given date, type, gr1 and gr2 combination there is an amount specified. If the next date doesn't have the group specified on the previous date (for example, 2003 doesn't have row 2). I need to create a row for 2003 and add the amount from row 2 (0.0004 + 0.001=0.0014).
Here is the desired output:
10-05-2017 04:44 PM
The lag function maintains a queue of values and returns the previously stored value each time you call it. If you call it unconditionally, it will return the value from the previous observation.
10-05-2017 05:45 PM
Please describe you process in terms of the variables you show. It really is not clear how "next date doesn't have the group specified " in terms of grp1 and grp2 as there are two variables but you say "group specified" and since 2003 has 2 rows in the have data I am not sure what you mean by "2003 doesn't have row 2".
If you describe things in terms of which values, such as the previous year group with grp1= xxx and grp2=yyy then the problem gets much clearer.
But what if there were a 2002 set of records? Would you still be looking for the 2001? Or if the previous year group is 3 years earlier such as 2001 followed by 2004. Would you want data from 2003 (which may not exist) or 2002 (which also doesn't exist) or 2001?