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
- /
- How to create a record counter variable

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

3 weeks ago - last edited 3 weeks ago by Reeza

Okay SAS community, I'm very green in this field of Data. I have specific field expertise, but lack the data science background, so I need a little direction:

I'm trying to apply sequential identifiers to a column of existing ID numbers in order to later filter out the first ID number in the original column. There are roughly 30 columns of total data and about 500K records...big table.

Here are the existing ID numbers in my table:

6JGM92501240

6JGM92501240

6JGM92301228

6JGM92001215

6JGM92001215

6JGM92001215

6JGM92001215

6JGM92301228

6JGM92301228

I would like to have this:

1 6JGM92501240

2 6JGM92501240

1 6JGM92301228

1 6JGM92001215

2 6JGM92001215

3 6JGM92001215

4 6JGM92001215

1 6JGM92301228

2 6JGM92301228

I can't simply use select distinct rows only due to some other factors. Any idea how to do this?? Any help would be great, thanks!

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

Posted in reply to LeftHandLob

3 weeks ago

This post goes over how to enumerate BY groups:

https://stats.idre.ucla.edu/sas/faq/how-can-i-create-an-enumeration-variable-by-groups/

@LeftHandLob wrote:

Okay SAS community, I'm very green in this field of Data. I have specific field expertise, but lack the data science background, so I need a little direction:

I'm trying to apply sequential identifiers to a column of existing ID numbers in order to later filter out the first ID number in the original column. There are roughly 30 columns of total data and about 500K records...big table.

Here are the existing ID numbers in my table:

6JGM92501240

6JGM92501240

6JGM92301228

6JGM92001215

6JGM92001215

6JGM92001215

6JGM92001215

6JGM92301228

6JGM92301228

I would like to have this:

1 6JGM92501240

2 6JGM92501240

1 6JGM92301228

1 6JGM92001215

2 6JGM92001215

3 6JGM92001215

4 6JGM92001215

1 6JGM92301228

2 6JGM92301228

I can't simply use select distinct rows only due to some other factors. Any idea how to do this?? Any help would be great, thanks!

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

Posted in reply to LeftHandLob

3 weeks ago

```
proc sort data=have;
by id;
run;
data want;
set have;
by id;
if first.id then seq=0;
seq+1;
run;
```

If the data is already sorted, you can leave out the PROC SORT.

--

Paige Miller

Paige Miller

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

Posted in reply to LeftHandLob

3 weeks ago

Just a note that I modified your subject line to make it more descriptive.

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

Posted in reply to LeftHandLob

3 weeks ago

```
data have;
input id $20.;
cards;
6JGM92501240
6JGM92501240
6JGM92301228
6JGM92001215
6JGM92001215
6JGM92001215
6JGM92001215
6JGM92301228
6JGM92301228
;
data want;
set have;
by id notsorted;
if first.id then n=1;
else n+1;
run;
```