topic Re: How to give unique id to group of similar values in a column? in SAS Procedures

How to give unique id to group of similar values in a column?

koya — Wed, 04 Mar 2015 03:26:42 GMT

data:

accid

====

abc

def

abc

xyz

def

desired output:

accid uniqid

==== =====

abc 1

def 2

abc 1

xyz 3

def 2

Re: How to give unique id to group of similar values in a column?

RamKumar — Wed, 04 Mar 2015 07:41:53 GMT

Try this.

data have;

input accid $;

datalines;

abc

def

abc

xyz

def

run;

proc sort data=have;

by accid;

run;

data want (rename=count=uniqid);

set have;

by accid;

if first.accid then count+1;

else count=count;

run;

Re: How to give unique id to group of similar values in a column?

LinusH — Wed, 04 Mar 2015 11:56:53 GMT

Just by looking in your example, it looks like a surrogate key.

What's the application/requirement? This will just create 1-1 relationships.

If you want to work with lots of data and giving them surrogate keys (and more), look an ETL tool such as DI Studio.

Re: How to give unique id to group of similar values in a column?

data_null__ — Wed, 04 Mar 2015 13:18:57 GMT

If you don't want to sort you could do something like this. If you have multiple keys this will also work by creating a compound index and adding variables to the CLASS statement.

data have;
   input accid $;
   cards; 
abc
abc
def
abc
xyz
def
;;;;
   run; 

proc summary data=have nway; 
   class accid;
   output out=uniqid(drop=_type_ _freq_ rename=(_level_=uniqid) index=(accid)) / levels; 
   run; 
proc print; 
   run; 
data withid;
   set have;
   set uniqid key=accid/unique; 
   run; 
proc print; 
   run;

Re: How to give unique id to group of similar values in a column?

data_null__ — Wed, 04 Mar 2015 13:34:44 GMT

Does

else count=count;

do anything?

Re: How to give unique id to group of similar values in a column?

koya — Wed, 04 Mar 2015 15:59:41 GMT

Thank you all, that helps!