BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
gudelake
Calcite | Level 5

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
mkeintz
PROC Star

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

View solution in original post

3 REPLIES 3
ballardw
Super User

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
PROC Star

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
gudelake
Calcite | Level 5

It worked. Thanks a lot!!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 796 views
  • 1 like
  • 3 in conversation