Use Date Field to find last N occurances before the date field in another dataset

[Tommy1]

Hi SAS Users,

Here are two sample datasets:

data Data1;
Input ID MonthEnd :MMYYN.;
Format MonthEnd :MMYYN.;
datalines;
1 200801
1 200802
1 200803
2 201501
2 201502
2 201503
;
data Data2;
Input ID Process_Date :Date9. Grade;
Format Process_Date :Date9.;
datalines;
1 01Aug2007 1
1 01Sep2007 2
1 01Oct2007 3
1 01Nov2007 4 
2 13Dec2014 1
2 11Jan2015 2
2 18Jan2015 3
2 03Feb2015 4
;

What I want to do is select the 3 most rescent occurances of the Grade from Data2 that happen before the MonthEnd from Data1 and transpose them into Data1. My idea of how I would like my final data to look like would be 

ID  MonthEnd Previous_Grade1 Previous_Grade2 Previous_Grade3

1    200801              4                                3                             2

1    200802              4                                3                             2

1    200803              4                                3                             2

2    201501              1                                 

2    201502              3                                2                             1

2    201503              4                                3                             2

The reason why all the ID=1 get assigned the same grades is because all the Process_Dates from Data2 (ID1) are less than all the MonthEnd dates so it takes the three most recent grades.

For ID=2 that has a MonthEnd=201501, there is only one ProcessDate that is before that month.

For ID=2 that has a MonthEnd=201502 and MonthEnd=201503, it takes the 3 most recent that are before that specific MonthEnd.

I am having a hard time explaining what I need so please feel free to ask any questions if you need clarification.

Thanks,

Tom

[34reqrwe · Accepted solution]

data Data1;
	Input ID MonthEnd yymmn6.;
	Format MonthEnd yymmn6.;
	datalines;
	1 200801
	1 200802
	1 200803
	2 201501
	2 201502
	2 201503
	;
run;

data Data2;
	Input ID Process_Date Date9. Grade;
	Format Process_Date Date9.;
	datalines;
	1 01Aug2007 1
	1 01Sep2007 2
	1 01Oct2007 3
	1 01Nov2007 4 
	2 13Dec2014 1
	2 11Jan2015 2
	2 18Jan2015 3
	2 03Feb2015 4
	;
run;

proc sql; 
	create table data3 as 
	select * 
	from 
	data1 
	left join 
	data2 
	on data1.id=data2.id
	and process_date < monthend
	order by id , monthend , process_date desc
	;
quit;

data data4(where=(cnt le 3));
	set data3;
	by id monthend descending process_date;
	retain cnt;
	if first.monthend then cnt=1;
	else cnt+1 ; 
run;

proc transpose data=data4 out=want (drop=_name_); 
	by id monthend ;
	var grade;
	id cnt;
run;

[Tommy1]

@34reqrwe  That was such an elegant solution. Thanks for all your help!