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
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;
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;
@34reqrwe That was such an elegant solution. Thanks for all your help!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.