Desktop productivity for business analysts and programmers

Distinct numbering

Reply
Super Contributor
Posts: 398

Distinct numbering

I have a table that looks like this
A
A
A
B
B

How can I create an id column that will look like this
1 A
1 A
1 A
2 B
2 B

Thank you for your help
SAS Employee
Posts: 149

Re: Distinct numbering

First./last. processing using DATA step code would be the easiest method. I can't think of a good way to do this via the query builder or a data task... but maybe someone else will come up with something there. Smiley Happy

/* Create data as an example for this */
data temp;
input charcol : $1.;
datalines;
A
A
A
B
A
C
;
run;

/* Pre-sort the data by the variable in question, very important */
proc sort data=temp out=tempsort;
by charcol;
run;

/* Use a BY statement for the sorted variable */
data temp2;
set tempsort;
by charcol;
if first.charcol=1 then id+1;
run;
Super Contributor
Posts: 398

Re: Distinct numbering

Richard,
Thank you so much that was it.

I have to find a way to grasp the way sas handles data compared to other DBMS.

Thank you again
Super Contributor
Posts: 398

Re: Distinct numbering

Richard,
Sorry to bother but I have a follow up question based off of the first one.

Say now I have a second column that has data in it

A A
A A
A B
A C
B A
B C
C A
C A
C A
C B

I need the ids to do the same for the second column kind of like we did for the first.

A A 1 1
A A 1 1
A B 1 2
A C 1 3
B A 2 1
B C 2 2
C A 3 1
C A 3 1
C A 3 1
C B 3 2

Sorry if this is confusing. I have 4 columns that I have to id this way.

Thank You
Super Contributor
Posts: 398

Re: Distinct numbering

I have run into another issue with the way my data needs to be numbered and I can't seem to find a way to do it.

If I have
A A
A B
A
A C
A D

I need a way for my numbering to skip the blanks without adding 1 to the counter.

A A 1 1
A B 1 2
A 1
A C 1 3
A D 1 4

I need my counter to not increment on the 3rd row so the 4th row will continue from the id on the second row.

Any help would be greatly appreciated.

Thank You
Contributor
Posts: 28

Re: Distinct numbering

Something like this should work. May not be exactly what you are looking for, but let me know.

/* Create data as an example for this */
data temp;
infile datalines dsd delimiter=',';
attrib charcol1 length = $1
charcol2 length = $1;
input charcol1 $
charcol2 $;
datalines;
A,A
A,B
A,
A,C
A,D
;
run;

/* Pre-sort the data by the variable in question, very important */
proc sort data=temp out=tempsort;
by charcol1 charcol2;
run;

/* Use a BY statement for the sorted variable */
data temp2;
set tempsort;
retain charcol1 charcol2;
by charcol1 charcol2;
if first.charcol1 then id+1;
if first.charcol2 and charcol2 NE ' ' then id2+1;
if id2=0 then id2=.;
run;

A 1 .
A A 1 1
A B 1 2
A C 1 3
A D 1 4
Super Contributor
Posts: 398

Re: Distinct numbering

jklein,

Thank you so much for the help. Your code helped me get it working.

Thanks
Jerry
Ask a Question
Discussion stats
  • 6 replies
  • 145 views
  • 0 likes
  • 3 in conversation