Take value in current row and impute it into previous row

Moderators: statman, Analyst Techy, andris, Fierce, GerineL, Smash

0101
Posts: 52
Joined: Fri Mar 04, 2011 9:42 pm

Take value in current row and impute it into previous row

Postby 0101 » Fri Oct 12, 2012 6:42 pm

I am trying to take the value of a variable in Row X and impute it into a variable in Row X-1.

For example, the child below has three rows, one for each grade he was in school. The date he graduated from Grade 1 (CurrentGradDate) is missing. But it is listed (as PrevGradDate) in his 2nd record: 5/30/10.

Code: Select all

School Child Grade PrevGradDate  CurrentGradDate 
1      5     1     NA
1      5     2     5/30/10       5/15/11
1      5     3     5/15/11       6/1/12
In this case, I want to impute 5/30/10 as the CurrentGradDate for his 1st row.

My data contains many schools and children, and sometimes due to data quality a child's grade may skip (from 1 to 3), so I need to account for that.

The code I have so far will identify records that have a PrevGradDate which should be copied into the previous record (CurrentGradDate). Example below. But I'm just not sure how to do that. I tried playing with the lead function but could not get it to work.

Code: Select all

if School = lag(School) AND ChildID = lag(ChildID) AND (lag(Grade) = Grade - 1) AND not (missing(PrevGradDate)) AND missing(lag(CurrentGradDate)) Flag = 1.

Code: Select all

School Child Grade PrevGradDate  CurrentGradDate Flag
1      5     1     NA
1      5     2     5/30/10       5/15/11        1
1      5     3     5/15/11       6/1/12          
------------------------------------------------------
2      6     1     NA
2      6     3     5/28/06       6/2/07
------------------------------------------------------
3      8     2     6/1/02
3      8     3     6/1/03                        1
3      8     4     5/30/04                       1
Suggestions?
Penguin_Knight
Posts: 473
Joined: Thu Apr 05, 2012 5:58 pm

Re: Take value in current row and impute it into previous ro

Postby Penguin_Knight » Fri Oct 12, 2012 8:44 pm

LEAD() function is deprecated. Try ranking your data in descending order of date, then apply LAG().

Who is online

Users browsing this forum: No registered users and 2 guests

cron