Creating unique ID for people with multiple entries, but sequential between people

[Salsalvr]

I'm looking for a way to create a unique Identifier that will account for duplicates of a client based on an old client ID.  Please see below.

I tried a do loop but it just hung up on me.  Does anyone know code for this. Thanks.

Old _ClientID    NewID

564                         1

564                         1

345                         2

367                         3

321                         4

111                         5

111                         5

111                         5

[Astounding · Accepted solution]

If you are starting with Old_ClientID and want to get NewID, you can use:

data want;

set have;

by Old_ClientID notsorted;

if first.Old_ClientID then NewID + 1;

run;

It's important that the incoming data is grouped properly.  For example, all the "564" values have to appear consecutively within the incoming data.

[Salsalvr]

Thank you so much. That worked perfectly.

[kiranv_]

 way to do this

proc sql;
create table want as 
select Old_ClientID , 
	   (select count(distinct Old_ClientID) from 
	   have b
where  a.Old_ClientID <= b.Old_ClientID) as newid 
from have a
order by newid;