topic Re: Create unique category ID in SAS Programming

Create unique category ID

rah1992 — Fri, 19 Nov 2021 01:02:37 GMT

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
;

Re: Create unique category ID

PaigeMiller — Fri, 19 Nov 2021 01:33:16 GMT

Why do you need to do work to create a redundant variable named category_ID? What's the point?

Re: Create unique category ID

Reeza — Fri, 19 Nov 2021 01:55:42 GMT

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
;

Re: Create unique category ID

rah1992 — Fri, 19 Nov 2021 04:17:14 GMT

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.

Re: Create unique category ID

PaigeMiller — Fri, 19 Nov 2021 11:15:52 GMT

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