Numbering items for each subgroup within a larger list

hantran99
hantran99 used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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;
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.
O I forgot to retain the value:

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

Author

Commented:
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.

Author

Commented:

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
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..

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial