Hello all, For each application_id, I expect to 1) identify the applicant which applied the application_id and then 2) identify application_id which applied by applicants over the past 5 years. By using the following data as an example, Obs, Applicant_name application_date application_id 1,A, 2021-07-28, 101 2,B, 2021-07-28, 101 3,A, 2017-07-28, 102 4,A, 2016-07-27, 103 5,B, 2018-07-28, 104 For observation application_id 101, it is applied by two applicants, which are A and B. and then I expect to include application_id 101, because it is applied by applicant A on 2017-07-28, which is within 5 years before 2021-07-28. I include application_id 104 for the same reason. I do not include application_id 103, because although it is applied by applicant A, it is not applied within 5 years before 2021-07-28. In the end, for application_id 101, I expect to get table like below, Obs,Applicant_name,application_date,application_id,applied_id 1,A, 2021-07-28, 101, 102 1,A, 2021-07-28, 101, 104 2,B, 2021-07-28, 101, 102 2,B, 2021-07-28, 101, 104 For application_id 102, I expect to have Obs,Applicant_name,application_date,application_id,applied_id 3,A, 2017-07-28, 102, 103 For application_id 103, I expect to have Obs,Applicant_name,application_date,application_id,applied_id 4,A, 2016-07-27, 103 So, in the end, I expect to have Obs,Applicant_name,application_date,application_id,applied_id 1,A, 2021-07-28, 101, 102 1,A, 2021-07-28, 101, 104 2,B, 2021-07-28, 101, 102 2,B, 2021-07-28, 101, 104 3,A, 2017-07-28, 102, 103 4,A, 2016-07-27, 103 Could you please give me some advice about it. Many thanks in advance.
data have;
infile cards dsd dlm=",";
input
Obs :8.
Applicant_name :$8.
application_date: YYMMDD10.
application_id :8.
;
format
application_date :YYMMDD10.
;
cards;
1,A,2021-07-28,101
2,B,2021-07-28,101
3,A,2017-07-28,102
4,A,2016-07-27,103
5,B,2018-07-28,104
;;;;
run;
... View more
