asked on

Numbering items for each subgroup within a larger list

Hi,

I am looking for a way in SAS to do the following.

Say you have the following dataset

cust_number transno
A abc123
A def456
A fed666
B bbb444
B ttt567
B ert444
B eee444
C tyk333
C kyt567

I would like to number all the records for each customers as follows:
cust_number transno transcounter
A abc123 1
A def456 2
A fed666 3
B bbb444 1
B ttt567 2
B ert444 3
B eee444 4
C tyk333 1
C kyt567 2

As you can see, I want to be able to number of observations in each subgroup (in this case customers) 1, 2, 3, 4 and so on and then restart from 1 again for the next customer or subgroup and so. How can I accomplish this in SAS.

wolfen351

step 1: proc sort the dataset

step 2:

data newset;
set sortedset;
by cust_number transno;
if first.transno then X = 0;
X=X+1;
run;

wolfen351

The secret here is the BY statement, it creates first. and last. for each group in the file. Then you can do an If on the First. and Last.

ASKER CERTIFIED SOLUTION

wolfen351

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

hantran99

ASKER

Hi Wolfen351,
This is helpful. I will give it a go. I sort of knew that I had to use the retain statement somewhere but not sure exactly where.

Thanks again.

hantran99

ASKER

Hi Wolfen351,

Doesn't seem to work. The first row shows up with 1, but then missing values fills up the rest of the members in the subgroup. Do you know why that is so.

Thanks

wolfen351

I think your retain is not working. It needs to be a value that is not in the dataset. You can also use X=Sum(X,1) instead of X=X+1 to deal with missing values.

If you paste your code here, i'm sure I will be able to help spot the error..

You also need to make sure the dataset is sorted, otherwise the BY statement has no meaning..