topic Re: How to restrict proc sql to pick up current data? in SAS Programming

How to restrict proc sql to pick up current data?

inquistive — Tue, 23 Nov 2021 16:33:47 GMT

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;

Re: How to restrict proc sql to pick up current data?

HB — Tue, 23 Nov 2021 19:13:05 GMT

This looks as much a database design problem as anything else.

I see subject name, pay code, stream code, start date and end date all both tables, which looks bad on the face of it from here. A table structure with a student table (primary key student id, perhaps demographic elements) and an employment table (multiple records per student allowed, with start dates, end dates, stream, and pay code for each record) might make things easier.

Perhaps some sample data could be provided and the desired result shown based on the provided data?

Re: How to restrict proc sql to pick up current data?

ChrisNZ — Wed, 24 Nov 2021 07:04:33 GMT

what's variable SECTION ?

Re: How to restrict proc sql to pick up current data?

Kurt_Bremser — Wed, 24 Nov 2021 08:54:01 GMT

Please supply example data for both datasets that covers all possible combinations of interest. Use working data steps with datalines, NOTHING else. Post the codes using the "little running man" button (right next to the one indicated).