SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Accepted Solution Solved
Reply
Contributor
Posts: 51
Accepted Solution

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

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.


Screen Shot 2016-06-30 at 8.39.29 PM.png

Accepted Solutions
Solution
‎07-07-2016 03:03 PM
PROC Star
Posts: 1,167

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

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;

View solution in original post


All Replies
Solution
‎07-07-2016 03:03 PM
PROC Star
Posts: 1,167

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

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;

Contributor
Posts: 51

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

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 260 views
  • 0 likes
  • 2 in conversation