Help using Base SAS procedures

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

Reply
Occasional Contributor
Posts: 10

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

data:

accid

====

abc

abc

def

abc

xyz

def

desired output:

accid       uniqid

====      =====

abc            1

abc             1

def              2

abc            1

xyz            3

def             2

Regular Contributor
Posts: 168

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

Try this.

data have;

input accid $;

datalines;

abc

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;

Respected Advisor
Posts: 3,777

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

Does

else count=count;

do anything?

Super User
Posts: 5,257

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

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.

Data never sleeps
Respected Advisor
Posts: 3,777

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

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;

3-4-2015 7-17-51 AM.png
Occasional Contributor
Posts: 10

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

Thank you all, that helps!

Ask a Question
Discussion stats
  • 5 replies
  • 1053 views
  • 6 likes
  • 4 in conversation