11-25-2013 04:34 PM
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.
05-25-2018 06:11 AM
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;