define new variables based on time in longitudinal data

[gudelake]

Hi, 

I have a longitudianl data set with variables ID, days, outcome; Every ID has several measurement at different days. For example, one patient had vistis at day 1, 3, 7, 16, 20 and also outcome measurements on these days. Another patient had visits on day -2, 4, 6, 11, etc.  I want to take out visits that is closest to day 0 and day 7, day 14, in the range of +-2 days, etc. and make data look like:

ID, week, outcome

1, 0, 20

1, 1, 26

1, 2, 30

2, 0, 23

2, 1, NA

2, 2, 45

... 

Currently I am using if-else statement and extract data at each time point into a separate data set and then merge those data sets. Any better solutions?

Thanks. 

[ballardw]

I am concerned a bit about how do you get day = -2.

It would help to have some example data of the before data set. Best is to provide the example as a data step so we can generate a duplicate data set to test code. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

I am not sure from your description whether the shown data is the data to remove or to keep. And if there are more key days than 0, 7 and 14 then you need to list them.

[mkeintz · Accepted solution]

You apparently want to keep the day closest to a multiple of 7, but only if it is no more than 2 days before or 2 after that multiple.  Assuming your data are sorted by ID then this (untested) code should work:

data want (drop=I D keepobs: mindist:);

  array keepobs{0:10};
  array mindist{0:10};

  do I=1 by 1 until (last.id);
    set have;
    by id;

    WEEK=round(day/7);
    D=abs(day-7*WEEK);

    if D<=2 then do;
      ** if d=mindist{week} then continue;
      mindist{WEEK}=min(d,mindist{WEEK});
      if d=mindist{WEEK} then keepobs{WEEK}=I;
    end;
  end;

  do I=1 by 1 until (last.id);
    set have;
    by id;
	week=round(day/7);
    if I in keepobs then output;
  end;

run;

Notes:

1. This data reads all the records for a given id, tracking which is the closest to each reference day (0,7,14,...).  That is the first "do until" loop.
2. The second "do until" loop rereads the same records, but only outputs those which satisfy the "closest" criterion.
3. I allow for 11 weeks, from week 0 to week10 (where week=round(day/7)).  For tracking purposes, there are two arrays:
   1. MINDIST  (which tracks the minimal qualifying distance for each week 0 to 10)
   2. KEEPOBS (which tracks the record associated with the correspoinding week)
4. If there are multiple days having the minimum distance to a given reference day, this program keeps the last match.  If you want the first match, uncomment the commented line.
5. The "if I in keepobs" statement simply tests whether record sequence I is in the keepobs array.

If you want to accomodate the possibiliy of, say, up to week 21, then just change the array bounds for MINDIST and KEEPOBS.

[gudelake]

It worked. Thanks a lot!!