How do I identify the first date and apply a counter to dates only when the value changes?

[Kels123]

Hi,

I am doing a study where I look at how subjects move between two different environments (e.g. hospital and home) over time. I have found a way to code all dates that correspond to time spent in the hospital as 0, and all dates that correspond to time spent at home as 1 (time=0, 0; time = 1). If someone moves from the hospital to the home and then back to the hospital, they might have values for time of time=0, 1, and then 0 again. However, in many cases there are redundant measurements, i.e. not every date corresponds to a movement (e.g. if someone gets blood drawn while in the hospital, their time variable will still = 0, such that they might have multiple time=0 in a row). Please see the attachment for example of what the data output currently looks like (note that there are subject ID numbers that I have truncated to protect confidentiality).

What I would like to do is define the time periods by assigning a counter to each date that respresents a change in environment. In other words, for each patient, I would like to label their first date where time=0 as period=0, their first date where time=1 as period=1, the first date (after period 2) where time=0 as period 3, and the first date (after period 3) where time=1 as period 4, and so on and so forth. However, because there are often multiple time=0's in a row and time=1's in a row, and "time" (the variable) switches between 0 and 1 instead of increasing over time, I can't figure out how to do this. In the attachment, the variable "period" I created for subject #48 should be = 1, then 2, then 3. But here you can see it is wrong (=1,1,2).

Alternatively, I could create a simpler database by just extracting out the first dates where "time" is different (e.g. focusing on the frst observation for subject #32 and ignoring the second observation, aka the one on 7/17/2012, since time=0 for both dates) so that each date represents a movement to a different environment, and then I could just apply a counter based on the subject ID and date. But I don't know how to do this either.

I realize this is a very convoluted question, but if any of this rings a bell, please let me know! Thanks so much.

[TomKari · Accepted solution]

This may get you going in the right direction. I'm assuming that the dates are sorted correctly.

Tom

data want;

set have;

by PatientNo notsorted Time notsorted;

retain Period;

if first.PatientNo

then Period = 1;

if ^first.PatientNo & first.Time

then Period = Period + 1;

run;

[Kels123]

Thank you so much! This was exactly what I was looking for.