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

Postby 0101 » Thu Apr 26, 2012 5:13 pm

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

Postby Penguin_Knight » Thu Apr 26, 2012 6:57 pm

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

Postby apeape » Thu Apr 26, 2012 7:02 pm

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

Postby 0101 » Mon Apr 30, 2012 6:57 am

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

Postby Penguin_Knight » Mon Apr 30, 2012 5:37 pm

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

Postby GerineL » Wed May 09, 2012 8:25 am

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

Postby Penguin_Knight » Wed May 09, 2012 11:45 am

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

Postby GerineL » Thu May 10, 2012 11:22 am

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

cron