Hi experts,
A student is currently enrolled in Management Stream with a start date of 08/28/2021 and end date of 12/31/2021. The student is switching stream from Science(S) to Management (M), paycode is also changing from 'H'(current) to 'L'(future) with a start date of 01/01/2022 and end date of 12/31/2026.
An Access database table has been maintained with two start dates(08/28/2021 and 08/01/2022), two end dates(12/31/2021 and 12/31/2026), two streams ('S' and 'M') and two pay codes ('H' and 'L') to account for the upcoming changes. The problem is the logic below is picking up (or probably mixing things up) the start dates, end dates, streams and pay codes randomly and thus producing fallouts(indicating errors/mismatch). Is there a way to force the logic/code to restrict it to look at the start and end dates, stream and paycode to the current records (08/28/2021,12/31/2021,'S','H') until 12/31/2021? And also need to tell it to check future records (01/1/2022,12/31/2026,'M','L') ON or AFTER 01/01/2022. Any help/idea is greatly appreciated.
proc sql; create table detail as select distinct a.student_ID, case when b.studentId = '' then 'Row not found in StudentInfo' else '' end as Row_Check, case when b.studentId ^= '' and a.subject_name ^= b.SubjName then 'Unmatched Subject Name' else '' end as Subject_Check, case when b.studentId ^= '' and a.pay_code ^= b.paycode then 'Unmatched Pay Code' else '' end as PayCode_Check, case when b.studentId ^= '' and a.stream_code ^= b.Streamcode then 'Unmatched Stream Code' else '' end as Stream_Check, case when b.studentId ^= '' and (a.start_date < b.StartDate) or (a.end_date > b.EndDate) then 'Date Range not Allowed' else '' end as EnrollmentDate_Check, a.student_name, a.subject_name, a.start_date, a.end_date, a.pay_code from student_data a left join StudentInfo b on b.studentId = a.Student_ID and b.section = a.section having row_check ^= '' or Subject_Check ^= '' or Paycode_Check ^= '' or Stream_Check ^= '' or EnrollmentDate_Check ^= '' order by a.student_ID, a.Student_Name;
quit;
... View more