SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

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

 

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


Accepted Solutions
Solution
‎04-24-2018 12:55 PM
Super User
Posts: 6,638

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

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.

View solution in original post


All Replies
Solution
‎04-24-2018 12:55 PM
Super User
Posts: 6,638

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

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.

New Contributor
Posts: 3

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

Posted in reply to Astounding
Thank you so much. That worked perfectly.
PROC Star
Posts: 503

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

 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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 440 views
  • 1 like
  • 3 in conversation