turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- BI
- /
- Enterprise Guide
- /
- Distinct numbering

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-08-2010 12:46 PM

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

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to jerry898969

06-08-2010 01:19 PM

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;

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to RichardH_sas

06-08-2010 01:34 PM

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

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to RichardH_sas

06-08-2010 01:41 PM

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

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to jerry898969

06-09-2010 09:13 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to jerry898969

06-09-2010 09:43 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to jklein271

06-09-2010 09:58 AM

jklein,

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

Thanks

Jerry

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

Thanks

Jerry