Assigning last grade when one not assigned for current date

[Tommy1]

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

[novinosrin]

Hi @Tommy1   If the objective is to look up date1yr which presumably a date one year in advance of the sas date and the associated grade, it's very straight forward

However

• 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.

Are you sure with highlighted portion with respect to ID5 is even relevant? Coz when processing ID5 the look up will not fetch the associated grade for date1yr. 

So my understanding is, look up using date1yr, fetch the associated grade, if not found assign the current grade as grade1yr grade. Please confirm

[Tommy1]

Hi @novinosrin,

Thanks for the reply. You are correct in your understanding that if there is no grade associated with the Date1yr date then to use the current grade. I see how my original description can be confusing and the highlighted part doesn't need to be explicitly stated.

Thanks,

Tom

[novinosrin · Accepted solution]

Hi @Tommy1 

Thank you & here you go:

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;

data want;
set test;
if _n_=1 then do;
   dcl hash H (dataset:'test(rename=(grade=Grade1yr))',ordered: "A") ;
   h.definekey  ("SASDate") ;
   h.definedata ("Grade1yr") ;
   h.definedone () ;
end;
if h.find(key:Date1yr) ne 0 then Grade1yr=grade;
run;

[Tommy1]

@novinosrin Thanks for a simple solution!

[novinosrin]

You are very welcome!

[shreyansh241990]

Regarding the same problem can we do it using proc sql or Array. Cause i tried it using proc sql but able to get those grade which match if i try to get other it gives us numerous other possible outcomes. Can you tell if it is possible using array or proc sql;

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;

proc sql;
select a.SASDate, b.Date1yr,
case when a.SASDate=b.Date1yr then a.Grade
end as Grade1yr from test a , test b order by ID;
quit;
run;

[novinosrin]

Indeed you can. But array isn't a good approach though. The lookup approach here whether proc sql/hash/merge etc is a key based. While you can load in array, loop through with a linear approach, that's not ideal. 

I appreciate your thoughts

[novinosrin]

@shreyansh241990   This is how you do using proc sql

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;

proc sql;
create table want as
select a.*,coalesce(b.grade,a.grade) as Grade1yr       
from test a left join test(keep=SASDate grade) b
on Date1yr=b.SASDate;
quit;

[shreyansh241990]

Thank you soo much it worked .If you have any idea regarding links where i can practice the concepts with problems it will be very helpful and once again thank you for the approach.

[novinosrin]

My approach was/is to read books by author ron cody. You could try that too.

SAS^® University Edition Compatible

Learning SAS^® by Example: A Programmer's Guide, Second Edition

By Ron Cody