topic Distinct numbering in SAS Enterprise Guide

Distinct numbering

jerry898969 — Tue, 08 Jun 2010 16:46:37 GMT

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

Re: Distinct numbering

RichardH_sas — Tue, 08 Jun 2010 17:19:21 GMT

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. 🙂

/* 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;

Re: Distinct numbering

jerry898969 — Tue, 08 Jun 2010 17:34:55 GMT

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

Re: Distinct numbering

jerry898969 — Tue, 08 Jun 2010 17:41:44 GMT

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

Re: Distinct numbering

jerry898969 — Wed, 09 Jun 2010 13:13:06 GMT

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

Re: Distinct numbering

jklein271 — Wed, 09 Jun 2010 13:43:01 GMT

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

Re: Distinct numbering

jerry898969 — Wed, 09 Jun 2010 13:58:12 GMT

jklein,

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

Thanks
Jerry