I have two columns like this:
| subject | regnum |
|---|---|
| 106001 | 2 |
| 106001 | 2 |
| 106001 | 2 |
| 106001 | 1 |
| 106001 | 1 |
| 106001 | 4 |
| 106001 | 4 |
| 106001 | 6 |
| 106001 | 6 |
| 106001 | 3 |
| 106001 | 3 |
| 106001 | 5 |
| 106001 | 5 |
| 106001 | 7 |
| 106001 | 7 |
| 106001 | 8 |
and I want a new colmun like this:
| subject | regnum | regnum_new |
|---|---|---|
| 106001 | 2 | 1 |
| 106001 | 2 | 1 |
| 106001 | 2 | 1 |
| 106001 | 1 | 2 |
| 106001 | 1 | 2 |
| 106001 | 4 | 3 |
| 106001 | 4 | 3 |
| 106001 | 6 | 4 |
| 106001 | 6 | 4 |
| 106001 | 3 | 5 |
| 106001 | 3 | 5 |
| 106001 | 5 | 6 |
| 106001 | 5 | 6 |
| 106001 | 7 | 7 |
| 106001 | 7 | 7 |
| 106001 | 8 | 8 |
How can I do this?
>Solution :
data have;
input subject regnum;
datalines;
106001 2
106001 2
106001 2
106001 1
106001 1
106001 4
106001 4
106001 6
106001 6
106001 3
106001 3
106001 5
106001 5
106001 7
106001 7
106001 8
;
data want;
set have;
by subject regnum notsorted;
if first.regnum then regnum_new + 1;
if first.subject then regnum_new = 1;
run;