topic Re: Assigning last grade when one not assigned for current date in SAS Programming

Assigning last grade when one not assigned for current date

Tommy1 — Tue, 05 Feb 2019 19:08:38 GMT

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

Re: Assigning last grade when one not assigned for current date

novinosrin — Tue, 05 Feb 2019 19:24:07 GMT

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

Re: Assigning last grade when one not assigned for current date

Tommy1 — Tue, 05 Feb 2019 19:49:53 GMT

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

Re: Assigning last grade when one not assigned for current date

novinosrin — Tue, 05 Feb 2019 20:04:58 GMT

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;

Re: Assigning last grade when one not assigned for current date

Tommy1 — Tue, 05 Feb 2019 20:35:28 GMT

@novinosrin Thanks for a simple solution!

Re: Assigning last grade when one not assigned for current date

novinosrin — Tue, 05 Feb 2019 20:35:58 GMT

You are very welcome!

Re: Assigning last grade when one not assigned for current date

shreyansh241990 — Thu, 07 Feb 2019 20:20:35 GMT

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;

Re: Assigning last grade when one not assigned for current date

novinosrin — Thu, 07 Feb 2019 20:27:22 GMT

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

Re: Assigning last grade when one not assigned for current date

novinosrin — Thu, 07 Feb 2019 20:36:27 GMT

@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;

Re: Assigning last grade when one not assigned for current date

shreyansh241990 — Thu, 07 Feb 2019 20:41:21 GMT

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.

Re: Assigning last grade when one not assigned for current date

novinosrin — Thu, 07 Feb 2019 20:50:12 GMT

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

topic Re: Assigning last grade when one not assigned for current date in SAS Programming

Assigning last grade when one not assigned for current date

Re: Assigning last grade when one not assigned for current date

Re: Assigning last grade when one not assigned for current date

Re: Assigning last grade when one not assigned for current date

Re: Assigning last grade when one not assigned for current date

Re: Assigning last grade when one not assigned for current date

Re: Assigning last grade when one not assigned for current date

Re: Assigning last grade when one not assigned for current date

Re: Assigning last grade when one not assigned for current date

Re: Assigning last grade when one not assigned for current date

Re: Assigning last grade when one not assigned for current date

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

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