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
;
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 ;
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 ;
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.