Hi SAS Community, For each observation in my data there is a Grade,current date field called SASDate, and a field that is the date one year in the future, Date1yr. In a previous post (Attached Link), when the Date1yr field was equal to SASDate, then it would assign the grade one year in advance. If there was no place where the data matched up then it would assign the field to blank.The solution of left joining the data with itself works great. What I am trying to figure out how to do now is still assign the Grade1yr if SASDate=Date1yr, but if there is no grade one year in advance then assign the last recorded grade on file at that time . I will use the same code as last time to distinguish the differences in approach. Data Test;
Input ID Grade SASDate Date1yr;
Informat SASDate date9. Date1yr date9.;
Format SASDate date9. Date1yr date9.;
Datalines;
1 4 01Jan2010 01Jan2011
2 3 01Feb2010 01Feb2011
3 6 01Jan2011 01Jan2012
4 5 01Mar2011 01Mar2012
5 1 01Jan2012 01Jan2013
;
Run; The desired results are shown below (color coded to show how grades are assigned): ID Grade Grade1yr SASDate Date1yr 1 4 6 01Jan2010 01Jan2011 2 3 3 01Feb2010 01Feb2011 3 6 1 01Jan2011 01Jan2012 4 5 5 01Mar2011 01Mar2012 5 1 (1) 1 01Jan2012 (01Jan2012) 01Jan2013 ID 1 is assigned a grade of 6 for Grade1yr because in the SASDate in ID 3 is equal to the Date1yr in ID 1. ID 2 is assigned a grade of 3 for Grade1yr because it's Date1yr does not match any SASDate. The grade of 3 is the grade for the most current month so is the one assigned to Grade1yr For ID 5 it is used twice to inform grades. The first time it is used because the Date1yr from ID 3 matches the SASDate in ID 5. Then, there is is no SASDate that matches the Date1yr of ID 5 so the grade of 1 gets assigned to the Grade1yr for ID 5. I am happy to clarify if anything is confusing. Thanks for any help on this, Tom
... View more
