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:
date | type | grp1 | grp2 | cum_amt | |
2001 | . | . | . | 0.002 | |
2001 | MRA | TL08 | T630 | 0.0004 | |
2001 | MRA | TB08 | T630 | 0.0005 | |
2003 | . | . | . | 0.003 | |
2003 | MRA | TB08 | T630 | 0.0075 | |
2003 | MRA | TL08 | T630 | . | 0.0014 |
2005 | 0.0003 | ||||
2005 | MRA | TL08 | T630 | 0.0007 | |
2005 | MRA | TB08 | T630 | 0.0078 |
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.
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?
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.