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
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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.