topic Reorganizing data within subjects in a datafile in SAS Programming

Reorganizing data within subjects in a datafile

pronabesh — Mon, 25 Nov 2013 21:34:38 GMT

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

Re: Reorganizing data within subjects in a datafile

jim_cai — Fri, 25 May 2018 10:11:32 GMT

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;