Thank you so much ChrisNZ. I have tweaked the code a bit in order to only include questionnaires that were filled in after admission date. See the complete code below. I have only changed this line and date_of_ques between admission_date and Discharge_date + 365 . It seems to be working however I get this message when running the code. Should I be concerned? WARNING: Column named record_id is duplicated in a select expression (or a view). Explicit references to it will be to the first one. WARNING: Variable Record_id already exists on file WORK.WANT. Complete code: { proc sql; create table want as select have1.*, have2.* from have1 left join have2 on have1.Record_id = have2.Record_id and date_of_ques between admission_date and Discharge_date + 365 left join have1 next on have1.Record_id = next.Record_id and have1.admission_number+1 = next.admission_number and next.Admission_date < have2.date_of_ques where next.Record_id is null order by record_id, admission_number, numberquest ; quit; }
... View more