BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Fluorite | Level 6

## Create unique category ID

Without numerous if-then-else statements, how can you create a unique numeric category ID variable from the have data set?

``````data have;
input category \$ list \$;
datalines;
A Person1
A Person2
A Person3
A Person4
B Person5
B Person6
B Person7
B Person8
C Person9
C Person10
C Person11
C Person12
D Person13
D Person14
D Person15
D Person16
;

data want;
input category_id category \$ list \$;
datalines;
1 A Person1
1 A Person2
1 A Person3
1 A Person4
2 B Person5
2 B Person6
2 B Person7
2 B Person8
3 C Person9
3 C Person10
3 C Person11
3 C Person12
4 D Person13
4 D Person14
4 D Person15
4 D Person16
;``````
1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Create unique category ID

Also really curious as to why, but this is quite straightforward.

``````data have;
input category \$ list \$;
want = rank(category) - 64;
datalines;
A Person1
A Person2
A Person3
A Person4
B Person5
B Person6
B Person7
B Person8
C Person9
C Person10
C Person11
C Person12
D Person13
D Person14
D Person15
D Person16
;``````

I suspect this will not scale to your data though so another method to iterate it here:

``````
data want;
set have;
by category;
retain grouper 0;
if first.Category then grouper+1;
run;``````

@rah1992 wrote:

Without numerous if-then-else statements, how can you create a unique numeric category ID variable from the have data set?

``````data have;
input category \$ list \$;
datalines;
A Person1
A Person2
A Person3
A Person4
B Person5
B Person6
B Person7
B Person8
C Person9
C Person10
C Person11
C Person12
D Person13
D Person14
D Person15
D Person16
;

data want;
input category_id category \$ list \$;
datalines;
1 A Person1
1 A Person2
1 A Person3
1 A Person4
2 B Person5
2 B Person6
2 B Person7
2 B Person8
3 C Person9
3 C Person10
3 C Person11
3 C Person12
4 D Person13
4 D Person14
4 D Person15
4 D Person16
;``````

4 REPLIES 4
Diamond | Level 26

## Re: Create unique category ID

Why do you need to do work to create a redundant variable named category_ID? What's the point?
--
Paige Miller
Super User

## Re: Create unique category ID

Also really curious as to why, but this is quite straightforward.

``````data have;
input category \$ list \$;
want = rank(category) - 64;
datalines;
A Person1
A Person2
A Person3
A Person4
B Person5
B Person6
B Person7
B Person8
C Person9
C Person10
C Person11
C Person12
D Person13
D Person14
D Person15
D Person16
;``````

I suspect this will not scale to your data though so another method to iterate it here:

``````
data want;
set have;
by category;
retain grouper 0;
if first.Category then grouper+1;
run;``````

@rah1992 wrote:

Without numerous if-then-else statements, how can you create a unique numeric category ID variable from the have data set?

``````data have;
input category \$ list \$;
datalines;
A Person1
A Person2
A Person3
A Person4
B Person5
B Person6
B Person7
B Person8
C Person9
C Person10
C Person11
C Person12
D Person13
D Person14
D Person15
D Person16
;

data want;
input category_id category \$ list \$;
datalines;
1 A Person1
1 A Person2
1 A Person3
1 A Person4
2 B Person5
2 B Person6
2 B Person7
2 B Person8
3 C Person9
3 C Person10
3 C Person11
3 C Person12
4 D Person13
4 D Person14
4 D Person15
4 D Person16
;``````

Fluorite | Level 6

## Re: Create unique category ID

Works great. Thanks @Reeza. Need it to feed into a user-defined macro requiring a numeric unique ID column. Not my macro and don't have time now to edit it.

Diamond | Level 26

## Re: Create unique category ID

My solution

``````data want;
set have;
numeric_id=rank(category)-rank('A')+1;
run;``````

but maybe this fails if you have oversimplified the category variable

--
Paige Miller
Discussion stats
• 4 replies
• 561 views
• 0 likes
• 3 in conversation