## Create a number incremented by 1 for each unique pair

Moderators: statman, Analyst Techy, andris, Fierce, GerineL, Smash

0101
Posts: 52
Joined: Fri Mar 04, 2011 9:42 pm

### Create a number incremented by 1 for each unique pair

I have data like so:

Code: Select all

``````SubjectID EventID
1   1
1   1
-----
1   4
1   4
1   4
-----
2   1
2   1
2   1
2   1``````
(The separating lines are just there for clarity; they're not in the data.)

SubjectID represents a Subject. EventID represents an event for that subject. However, I need the EventIDs to be unique (notice that SubjectID 1 and 2 have a matching EventID).

So, I'd like to create a unique number (NewID) for every unique combination of SubjectID and EventID. Ideally, I'd like to start with 1 and then increment up. The result would look like:

Code: Select all

``````SubjectID EventID NewID
1   1   1
1   1   1
---------
1   4   2
1   4   2
1   4   2
---------
2   1   3
2   1   3
2   1   3
2   1   3``````
Any suggestions for going about doing this? Thank you.
Penguin_Knight
Posts: 473
Joined: Thu Apr 05, 2012 5:58 pm

### Re: Create a number incremented by 1 for each unique pair

Code: Select all

``````COMPUTE temp=subjectid * 100 + eventid .

AUTORECODE VARIABLES=temp
/INTO newid .
VALUE LABELS newid .
EXECUTE .

DELETE VARIABLES temp .
EXECUTE .``````
apeape
Posts: 56
Joined: Mon May 02, 2011 6:07 pm

### Re: Create a number incremented by 1 for each unique pair

This should work:

Code: Select all

``````sort cases by subjectid eventid.
compute #a = (subjectid = lag(subjectid)).
compute #b = (eventid = lag(eventid)).
if (\$casenum = 1) newid = 1.
if (#a = #b) newid = lag(newid).
if (#a <> #b or #a + #b = 0) newid = lag(newid)+1.
exe.
``````
0101
Posts: 52
Joined: Fri Mar 04, 2011 9:42 pm

### Re: Create a number incremented by 1 for each unique pair

Thank you! I went with apeape's approach.

The approach that uses arithmetic (COMPUTE temp=subjectid * 100 + eventid .) sometimes missed unique pairs, because they both happened to compute an identical temp value based on the subjectID and eventID.

For example, SubjectID of 7 and EventID of 10 returns a temp of 7 * 100 + 10 = 710.

A SubjectID of 6 and EventID of 110 returns a temp of 6 * 100 + 110 = 710.

My dataset has about 500,000 records so this was likely to occur a few times.
Penguin_Knight
Posts: 473
Joined: Thu Apr 05, 2012 5:58 pm

### Re: Create a number incremented by 1 for each unique pair

0101 wrote:Thank you! I went with apeape's approach.

The approach that uses arithmetic (COMPUTE temp=subjectid * 100 + eventid .) sometimes missed unique pairs, because they both happened to compute an identical temp value based on the subjectID and eventID.

For example, SubjectID of 7 and EventID of 10 returns a temp of 7 * 100 + 10 = 710.

A SubjectID of 6 and EventID of 110 returns a temp of 6 * 100 + 110 = 710.

My dataset has about 500,000 records so this was likely to occur a few times.
The *100 is arbitrary. Basically, it just serves to chain the two IDs together. If you second ID has the most three digits, then multiply the first ID by 1000. Then you'll only get 7010 and 6110.
GerineL
Moderator
Posts: 1477
Joined: Tue Jun 10, 2008 4:50 pm

### Re: Create a number incremented by 1 for each unique pair

apeape wrote:This should work:

Code: Select all

``````sort cases by subjectid eventid.
compute #a = (subjectid = lag(subjectid)).
compute #b = (eventid = lag(eventid)).
if (\$casenum = 1) newid = 1.
if (#a = #b) newid = lag(newid).
if (#a <> #b or #a + #b = 0) newid = lag(newid)+1.
exe.
``````
What does that #a thing mean? I've never seen it before in a syntax!
Penguin_Knight
Posts: 473
Joined: Thu Apr 05, 2012 5:58 pm

### Re: Create a number incremented by 1 for each unique pair

Gutnre wrote:
What does that #a thing mean? I've never seen it before in a syntax!
It's called scratch variable. It's calculated and used in the background but never appended to the data set as visible variable. A good tool if you just want to make some make-do transitional variables.
GerineL
Moderator
Posts: 1477
Joined: Tue Jun 10, 2008 4:50 pm

### Re: Create a number incremented by 1 for each unique pair

That is very awesome, most of the syntaxes I write have a lot of ..._temp variables, and then "delete variable" .

### Who is online

Users browsing this forum: No registered users and 1 guest