Numbering Repeated Observations

Reply
Occasional Contributor EB1
Occasional Contributor
Posts: 10

Numbering Repeated Observations

Hi all,

I am trying to code an observation variable that will count the number of instances of an event. The problem is that each incidence of event has multiple observations. I tried coding by ID number, but ran into issues because some participants have 2 incidences of the event on different days (or same day, different times). Any ideas on how to do this? Any help is appreciated! Thanks!

 

ID      Day     Time

01       2        4:00

01       2        4:00 

01       2        7:00

01       2        7:00

02       5        1:00

02       5        1:00

02       5        1:00

02       7        1:30

02       7        1:30

02       7        1:30

02       7        1:30

02       7        1:30

03       4        3:00

03       4        3:00

03       4        3:00

 

 

ID      Day     Time     Obsn

01       2        4:00         1

01       2        4:00         1

01       2        7:00         2

01       2        7:00         2

02       5        1:00         3

02       5        1:00         3

02       5        1:00         3

02       7        1:30         4

02       7        1:30         4

02       7        1:30         4

02       7        1:30         4

02       7        1:30         4

03       4        3:00         5

03       4        3:00         5

03       4        3:00         5

Esteemed Advisor
Posts: 5,198

Re: Numbering Repeated Observations

Untested:

Data want;
Set have;
By id day time;
If first.time then obsn+1;
Run;
Data never sleeps
Occasional Contributor EB1
Occasional Contributor
Posts: 10

Re: Numbering Repeated Observations

Hi LinusH, thank you so much for your imput. I realized just now however that I got the time variable miixed up - the hour and minute values are separated into separate columns, and the minute value changes for each datapoint. Any thoughts on how to handle this situation? Sorry for the confusion!

 

ID      Day     Hour    Minute

01       2          4          00

01       2          4          01

01       2          7          00

01       2          7          01

02       5          1          30

02       5          1          31

02       5          1          32

02       7          1          43

02       7          1          44

02       7          1          45

02       7          1          46

02       7          1          47

03       4          3          00

03       4          3          01

03       4          3          02

 

 

ID      Day     Hour    Minute    Obsn

01       2          4          00             1

01       2          4          01             1

01       2          7          00             2

01       2          7          01             2

02       5          1          30             3

02       5          1          31             3

02       5          1          32             3

02       7          1          43             4

02       7          1          44             4

02       7          1          45             4

02       7          1          46             4

02       7          1          47             4

03       4          3          00             5

03       4          3          01             5

03       4          3          02             5

Grand Advisor
Posts: 17,331

Re: Numbering Repeated Observations

The key to @LinusH solution is the BY variables.  It looks like your BY variables should be ID Day Hour, and then you can use FIRST.HOUR instead of th current IF.

 

Learning how BY variables work is worth the quick read, it's a very powerful mechanism in SAS:

 

http://support.sas.com/documentation/cdl/en/lrcon/62955/HTML/default/viewer.htm#a000761931.htm

Occasional Contributor EB1
Occasional Contributor
Posts: 10

Re: Numbering Repeated Observations

Thank you for your ideas Reeza, and for the link, that was very helpful! I have figured out how to code the majority of it (which I've included below), the only problem remains with participants who have multiple observations on the same day. For some reason, SAS does not recognize a new observation if the new observation starts only an hour or two after the first one ends. Any ideas what I can add to this code to fix that? Thank you!

 

data want;
set have;
by ID day hour;
if first.day and first.hour then number=0;
if first.day and first.hour then num+1;
run;

Grand Advisor
Posts: 17,331

Re: Numbering Repeated Observations

I don't understand what you mean, you'll have to provide some sample data that demonstrates your case.

Occasional Contributor EB1
Occasional Contributor
Posts: 10

Re: Numbering Repeated Observations

If I've got observations from different participants on different days, then the code works well and the Obsn value is correct:

 

ID      Day     Hour    Minute    Obsn

01       2          4          00             1

01       2          4          01             1

02       5          1          30             2

02       5          1          31             2

02       5          1          32             2

 

 

 

If I've got multiple observation from the same participant on different days, then the code works and the Obsn value is correct:

 

ID      Day     Hour    Minute    Obsn

01       2          4          00             1

01       2          4          01             1

01       3          7          00             2

01       3          7          01             2

 

 

 

The problem lies when I have multiple observations from the same participant on the same day. The code I currently have is not able to recognize that the new hour should cause Obsn to change. This is what is currently being outputted:

 

ID      Day     Hour    Minute    Obsn

01       2          4          00             1

01       2          4          01             1

01       2          5          30             1

01       2          5          31             1

 

However, I want it to look like this:

 

ID      Day     Hour    Minute    Obsn

01       2          4          00             1

01       2          4          01             1

01       2          5          30             2

01       2          5          31             2

 

Grand Advisor
Posts: 17,331

Re: Numbering Repeated Observations

if first.day and first.hour then number=0;
if first.day and first.hour then num+1;

 

Those are the same two conditions and the first record meets the conditions. So you first set it to 0 and then add 1, so your values are 1. Remove the first condition and you should be fine. You should really only need the line first.hour since the other are already included in your BY variables. 

 

 

data want;
set have;
by ID day hour;
if first.ID then number=0;
if first.hour then num+1;
run;
Occasional Contributor EB1
Occasional Contributor
Posts: 10

Re: Numbering Repeated Observations

 

You are correct, I will take that out. Even after running it with one line, however, it's still not able to identify participants with different observations on the same day!

 
Grand Advisor
Posts: 17,331

Re: Numbering Repeated Observations

My code didn't work? 

It most definitely can...I may suggest adding in variables that allow you to see what the first variables are and what your condition should be. 

 

 

Occasional Contributor EB1
Occasional Contributor
Posts: 10

Re: Numbering Repeated Observations

I know, I am surprised too because it should! I'm having a hard time knowing what to add to my code becuse there are instances where an observation will go across the hour, say from 10:30-11:30. In this case, the hour variable changes, however it is the same observation. Therefore to identify separate observations in the same day (i.e., one observation ending at 12:00 and another one starting at 1:00) I'm not sure I can use the hour variable as a marker...

Grand Advisor
Posts: 17,331

Re: Numbering Repeated Observations

Then you need to define your criteria better.  Include more data and provide what you've tried.

 

I think your original question was answered though, and it may be worth starting a new thread.

Ask a Question
Discussion stats
  • 11 replies
  • 565 views
  • 3 likes
  • 3 in conversation