DATA Step, Macro, Functions and more

Look at previous values and create new observation

Reply
Contributor
Posts: 47

Look at previous values and create new observation

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
SAS Super FREQ
Posts: 496

Re: Look at previous values and create new observation

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.

Super User
Posts: 12,996

Re: Look at previous values and create new 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?

Ask a Question
Discussion stats
  • 2 replies
  • 91 views
  • 0 likes
  • 3 in conversation