topic Re: Look at previous values and create new observation in SAS Programming

Look at previous values and create new observation

zqkal — Thu, 05 Oct 2017 19:43:41 GMT

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

Re: Look at previous values and create new observation

WarrenKuhfeld — Thu, 05 Oct 2017 20:44:02 GMT

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.

Re: Look at previous values and create new observation

ballardw — Thu, 05 Oct 2017 21:45:34 GMT

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?