topic Re: Reorganizing data within subjects in a datafile in SAS Data Management

Reorganizing data within subjects in a datafile

pronabesh — Mon, 25 Nov 2013 21:37:53 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.

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

Haikuo — Mon, 25 Nov 2013 22:28:35 GMT

It would be ideal that you have an sample output for your sample input data, so here is only my guess, if it is not meeting your requirement, then please provide your sample output:

data have;

input Subject_ID Follow_Up_Time (Interview_Date Previous_Interview_Date Adverse_Event_Report_Date) (:monyy5.);

format Interview_Date Previous_Interview_Date Adverse_Event_Report_Date monyy5.;

cards;

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 .

;

proc sql;

create table want as

select Subject_ID, Follow_Up_Time, Interview_Date, Previous_Interview_Date,

case when Previous_Interview_Date <= Adverse_Event_Report_Date <= Interview_Date

then Adverse_Event_Report_Date

when not (Previous_Interview_Date <= Adverse_Event_Report_Date <= Interview_Date )

and Follow_Up_Time = max(Follow_Up_Time) then max(Adverse_Event_Report_Date)

else . end as Adverse_Event_Report_Date format=monyy5.

from have

group by Subject_ID

order by Subject_ID,Follow_Up_Time

;

quit;

Haikuo

Re: Reorganizing data within subjects in a datafile

art297 — Mon, 25 Nov 2013 23:12:53 GMT

I, too, don't know if I correctly understand what you want, but the following is a data step method for doing something close to what Haikuo suggested. It differs, slightly, as it can assign multiple dates for a subject:

data have;

informat Interview_Date Previous_Interview_Date

Adverse_Event_Report_Date date9.;

format Interview_Date Previous_Interview_Date

Adverse_Event_Report_Date date9.;

input Subject_ID Follow_Up_Time Interview_Date

Previous_Interview_Date Adverse_Event_Report_Date;

cards;

1 1 10Feb2013 10Jan2013 10Nov2013

1 2 10Mar2013 10Feb2013 .

1 3 10Dec2013 10Nov2013 .

2 1 10Jun2013 10May2013 10May2013

2 2 10Jul2013 10Jun2013 11Jan2013

2 3 11Feb2013 11Jan2013 .

;

data want (drop=_:);

set have (rename=(Adverse_Event_Report_Date=_date));

retain _holddate;

format Adverse_Event_Report_Date date9.;

by Subject_ID;

if first.Subject_ID then call missing(_holddate);

if not missing(_date) then _holddate=_date;

if not missing(_holddate) and

(Previous_Interview_Date le _holddate le Interview_Date)

then Adverse_Event_Report_Date=_holddate;

run;

Re: Reorganizing data within subjects in a datafile

pronabesh — Tue, 26 Nov 2013 14:34:14 GMT

Thank You Arthur. This is exactly what I was looking for.

Re: Reorganizing data within subjects in a datafile

pronabesh — Tue, 26 Nov 2013 14:34:41 GMT

Thank You Haikuo. This is very helpful.

Re: Reorganizing data within subjects in a datafile

pronabesh — Tue, 03 Dec 2013 21:32:51 GMT

I thought I had the solution to this, but my problem is probably a little more complex. To demonstrate, I have listed one example from a single individual with multiple assessment points and described the variables below.

The purpose of this is to test the agreement between self_reported hospitalization (Hospitalization_date) and hospitalization from medical records (Admission_date). The assessment_date is the date of self reported assessment and lag_fu is the date of prior self reported assessment.

Now there are a couple of problems:

1. The admission_date variable is not properly merged to the corresponding period between the lag_fu and assessment_date

2. Each assessment date per person is listed thrice as there can be a max of three hospitalization records in that time period.

3. If there are multiple admission_date(s) at each assessment, all the dates need to be listed as shown in the output data set.

Your help is very much appreciated!

The input and output data set for one individual is:

Input:

Hospitalization_date	Record	Assessment_date	Admission_date	lag_fu
.	1	04NOV10	08DEC10	04OCT10
.	2	04NOV10	20JAN11	04OCT10
.	3	04NOV10	16DEC11	04OCT10
15DEC10	4	03JAN11	19DEC11	04NOV10
.	5	03JAN11	.	04NOV10
.	6	03JAN11	.	04NOV10
15JAN11	7	01MAR11	.	03JAN11
.	8	01MAR11	.	03JAN11
.	9	01MAR11	.	03JAN11
.	10	27APR11	.	01MAR11
.	11	27APR11	.	01MAR11
.	12	27APR11	.	01MAR11
.	13	29JUL11	.	27APR11
.	14	29JUL11	.	27APR11
.	15	29JUL11	.	27APR11
.	16	06OCT11	.	29JUL11
.	17	06OCT11	.	29JUL11
.	18	06OCT11	.	29JUL11
.	19	27MAR12	.	06OCT11
.	20	27MAR12	.	06OCT11
.	21	27MAR12	.	06OCT11
.	22	16MAY12	.	27MAR12
.	23	16MAY12	.	27MAR12
.	24	16MAY12	.	27MAR12

Output:

Hospitalization_date	Record	Assessment_date	Admission_date	lag_fu
.	1	04NOV10		04OCT10
.	2	04NOV10		04OCT10
.	3	04NOV10		04OCT10
08DEC10	4	03JAN11	08DEC10	04NOV10
.	5	03JAN11		04NOV10
.	6	03JAN11	.	04NOV10
20JAN11	7	01MAR11	20JAN11.	03JAN11
.	8	01MAR11	.	03JAN11
.	9	01MAR11	.	03JAN11
.	10	27APR11	.	01MAR11
.	11	27APR11	.	01MAR11
.	12	27APR11	.	01MAR11
.	13	29JUL11	.	27APR11
.	14	29JUL11	.	27APR11
.	15	29JUL11	.	27APR11
.	16	06OCT11	.	29JUL11
.	17	06OCT11	.	29JUL11
.	18	06OCT11	.	29JUL11
.	19	27MAR12	16DEC11	06OCT11
.	20	27MAR12	19DEC11	06OCT11
.	21	27MAR12	.	06OCT11
.	22	16MAY12	.	27MAR12
.	23	16MAY12	.	27MAR12
.	24	16MAY12	.	27MAR12

Re: Reorganizing data within subjects in a datafile

art297 — Wed, 04 Dec 2013 01:23:24 GMT

Pronabesh: totally un-cool removing a correct rating because you realized that you had a different problem than the one that you originally posted. In the future, I'd really hope that you simply create a new discussion.

That said, while I may have misinterpreted what you are trying to do, the following appears to provide your posted desired output:

data want (drop=_:);

array _dates(99) _temporary_;

do until (last.patient);

set have;

by patient;

if first.patient then do;

call missing(of _dates(*));

_counter=0;

end;

if not missing(Admission_date) then do;

_counter+1;

_dates(_counter)=Admission_date;

call missing(Admission_date);

end;

do until (last.patient);

set have (drop=Admission_date);

by patient;

if _counter gt 0 then do;

do _i=1 to _counter;

if (Assessment_date le _dates(_i) le lag_fu) or

(lag_fu le _dates(_i) le Assessment_date) then do;

Admission_date=_dates(_i);

call missing (_dates(_i));

_i=_counter;

end;

if not missing(Hospitalization_date) and not missing(Admission_date)

then Hospitalization_date=Admission_date;

else call missing(Hospitalization_date);

output;

call missing(Admission_date);

end;

run;

Re: Reorganizing data within subjects in a datafile

pronabesh — Wed, 04 Dec 2013 01:32:06 GMT

Hello Arthur,

Thank you for taking the time to help out on this. Sincere apologies for removing the correct rating. I am new to SAS communities and was under the false impression that a correct rating means that the discussion is closed. Will amend my mistake.

Once again your help and timely response is much appreciated!

Re: Reorganizing data within subjects in a datafile

art297 — Wed, 04 Dec 2013 01:41:30 GMT

Not a problem and I was pleased to see that you found my last post so quickly. Yes, a correct classification indicates that a question has been answered, however, if one discovers that the problem is more complex than originally posted, a new question/discussion should be started.

Re: Reorganizing data within subjects in a datafile

pronabesh — Wed, 04 Dec 2013 01:46:28 GMT

Thank you!

This also answers my question posted in the other thread about merging.

Best,

Pronabesh

Re: Reorganizing data within subjects in a datafile

art297 — Wed, 04 Dec 2013 02:04:36 GMT

Glad to hear that both of your posted question have been answered. I am all too familiar with the anguish involved in learning the expected protocol of a new site and completely understand. Hope you continue to post here.

Re: Reorganizing data within subjects in a datafile

pronabesh — Wed, 04 Dec 2013 16:13:09 GMT

Thank You Arthur.

This code worked!