## sequence

Frequent Contributor
Posts: 76

# sequence

Suppose I have d column in a sas dataset ,I need create a new dataset which contain the id column along with seq column

for id 101 the seq is 1 for 102 it is 2....if 101 again gets repeated the seq should be 1,for 102 it should be 2 ...The first assigned seq num should be repeated if same ids are repeated ..

id     seq
101   1
102   2
103   3
104   4
101   1
102   2
101   1
103   3

Super User
Posts: 9,599

## Re: sequence

Sort your data by ID, then do:

```data want;
set have;
by id;
retain seq;
seq=ifn(first.id,sum(seq,1),seq);
run;```
Super User
Posts: 10,280

## Re: sequence

Part of the following code might not be necessary, if you do not need to preserve the orginal order:

``````data have;
input id;
cards;
101
102
103
104
101
102
101
103
;
run;

data have;
set have;
order + 1;
run;

proc sort data=have;
by id;
run;

data want;
set have;
by id;
if first.id then seq + 1;
run;

proc sort data=want;
by order;
run;
``````
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Posts: 5,543

## Re: sequence

[ Edited ]

1) Create a list of unique Ids

2) Attribute distinct numbers to every Id

3) Add these numbers to original data

``````data have;
input id;
length otherData \$12;
otherData = catx(" ", "Other Data", _n_);
cards;
101
102
103
104
101
102
101
103
;

proc sql;
create table ids as
select unique id from have;
quit;

data idsSeq;
set ids;
seq +1;
run;

proc sql;
create table want as
select have.id, otherData, seq
from have natural join idsSeq;
quit;
``````

PG
Posts: 4,741

## Re: sequence

An approach using a hash table

``````data have;
input id;
cards;
101
102
103
104
101
102
101
103
;
run;

data want(drop=_:);
set have;
if _n_=1 then
do;
length seq 8;
dcl hash h1();
h1.defineKey('id');
h1.defineData('seq');
h1.defineDone();
end;

if h1.find() ne 0 then
do;
_seq+1;
seq=_seq;
end;
run;
``````
Posts: 1,345

## Re: sequence

You could also take advantage of the num_items attribute of hash objects:

if h1.find() ne 0 then
do;
seq=h1.num_items+1;