Hi:
I'm sure there is a way to do this in SQL, however, I can't think of it. I do have a DATA step solution to calculate a counter the way you want. First, I made some test data (called WORK.TESTDATA):
[pre]
Obs Name Age
1 Alfred 14
2 Alfred 19
3 Alfred 29
4 Alice 13
5 Alice 23
6 Barbara 13
7 Barbara 23
8 Carol 14
9 Carol 19
10 Carol 29
11 Henry 14
12 Henry 19
13 Henry 29
14 James 12
15 James 22
16 Jane 12
17 Jane 22
18 Janet 15
19 Janet 20
20 Janet 30
[/pre]
Next, I used this program to make the counter (called DUPCNT) and a copy of the original dataset with the DUPCNT variable added (data set is WORK.NEWTEST):
[pre]
data newtest;
retain dupcnt;
set testdata;
by name;
** set counter to 0 for first name;
if first.name then dupcnt = 0;
** output the record;
output;
** increment the counter -- if next name is the same;
** the new counter value will be used and output. Otherwise,;
** if diff name, the counter value restarts at 0 for every new name;
** the RETAIN statement keeps DUPCNT as a "running" total or cumulative counter;
dupcnt + 1;
run;
proc print data=newtest;
title 'After creating Duplicate counter';
var name age dupcnt;
run;
[/pre]
And the output looks like this:
[pre]
After creating Duplicate counter
Obs Name Age dupcnt
1 Alfred 14 0
2 Alfred 19 1
3 Alfred 29 2
4 Alice 13 0
5 Alice 23 1
6 Barbara 13 0
7 Barbara 23 1
8 Carol 14 0
9 Carol 19 1
10 Carol 29 2
11 Henry 14 0
12 Henry 19 1
13 Henry 29 2
14 James 12 0
15 James 22 1
16 Jane 12 0
17 Jane 22 1
18 Janet 15 0
19 Janet 20 1
20 Janet 30 2
[/pre]
If you need help with the DATA step program, you might consider contacting Tech Support. To use the program in EG, you'd have to put the code in a code node.
cynthia