Reorganizing data within subjects in a datafile

[pronabesh]

Hello All,

I need to reorganize a data set such that observations within subjects can be reordered by date.

The data set looks like this:

Subject_ID  Follow_Up_Time  Interview_Date  Previous_Interview_Date  Adverse_Event_Report_Date

1  1  Feb10  Jan10  Nov10

1  2  Mar10  Feb10  .

1  3  Dec10  Nov10  .

2  1  Jun10  May10  May10

2  2  Jul10  Jun10  Jan11

2  3  Feb11  Jan11  .

The last variable in the data set is not correctly assigned to its corresponding time period (i.e, Adverse_Event_Report_Date should be within the period between Interview_Date and Previous_Interview_Date). For example, in the first case line, the Adverse_Event_Report_Date variable should be missing and should appear in the third row for Subject_ID = 1. I

Please advice on the data steps required to restructure this data file.

Thank you in advance.

Sincerely,

Pronabesh

[jim_cai]

if you don't mind using PROC SQL instead of DATA Step:

data fu;
   input subject_id follow_up_time interview_date date9.
      +1 previous_interview_date date9.
      +1 adverse_event_report_date date9.;
   format interview_date previous_interview_date adverse_event_report_date date9.;
   datalines;
1 1 10FEB2018 10JAN2018 10NOV2018
1 2 10MAR2018 10FEB2018
1 3 10DEC2018 10NOV2018
2 1 10JUN2018 10MAY2018 10MAY2018
2 2 10JUL2018 10JUN2018 11JAN2018
2 3 10FEB2018 10JAN2018
   ;
run;

proc sql;
   create table fu_ae as
   select a.subject_id, a.follow_up_time, a.interview_date, a.previous_interview_date,
      b.adverse_event_report_date, b.follow_up_time as time_ae
   from fu as a left join (
      select * from fu where adverse_event_report_date^=.
   ) as b on a.subject_id=b.subject_id
   and a.previous_interview_date<=b.adverse_event_report_date<=a.interview_date
   order by subject_id, follow_up_time;
quit;