I highly recommend NOT using YYMM date format for your presentations, especially in year 2020 when 2001-2012 are easily construed as years instead of 2020 month 01-12.
You might also consider a grid report showing the grades (since joining).
The individual data sets can be easily stacked using a name range list syntax
SET T2001-T2005;
After the data is stacked, a merge by ID can combine join dates with the stack and filtered the grade values according to your rules
The YYMM date constructs can be read (i.e. parsed) into SAS date values using informat YYMMN4. The N in the informat name means there is No separator between year and month. I would recommend your date presentation be NLDATEYMM. (Jan 2020, Feb 2020, etc)
Example - Data for code
Data joindates;
input ID Join yymmN4.; format join NLDATEYMM.;
cards;
1 2001
2 2003
3 2003
4 2002
;
Data T2001;
input ID grade;
cards;
1 10
2 9
3 9
4 8
;
Data T2002;
input ID grade;
cards;
1 12
2 9
3 7
4 8
;
Data T2003;
input ID grade;
cards;
1 7
2 9
3 6
4 8
;
Data T2004;
input ID grade;
cards;
1 5
2 8
3 6
4 9
;
Data T2005;
input ID grade;
cards;
1 8
2 11
3 7
4 10
;
Example - Combine and Report
data stack(index=(id));
set T2001-T2005 indsname=table;
source = table;
date = input(scan(table, -1, 'T'),yymmN4.);
format date NLDATEYMM.;
drop source;
run;
data combined;
merge joindates stack;
by id;
grade_filtered = ifn(date < join, ., grade); months_since_join = 1 + intck('month', join, date); * value for transpose ID;
run;
ods html file='report.html' style=plateau;
proc report data=combined;
column id join grade_filtered,date;
define id / group;
define join / group;
define date / '' across order=data;
define grade_filtered / 'Grades' ;
run;
ods html close;
Output
If you want to create the pivoted data set (for reporting purposes?) a SQL and TRANSPOSE step is added.
proc sql;
create table stage1 as
select * from
( select id, join, grade, months_since_join
, catx('-',put(join,NLDATEYMM.),put(max(date),NLDATEYMM.)) as FollowUpPeriod length=17
from combined
group by id, join
) union
( select distinct 0, 0, 0, months_since_join from combined )
order by id, months_since_join
;
proc transpose data=stage1 prefix=g out=want(where=(id>0) drop=_name_);
by id join FollowUpPeriod;
id months_since_join;
var grade;
where months_since_join > 0;
run;
Result data set
... View more
