DATA Step, Macro, Functions and more

sequence

Reply
Contributor
Posts: 63

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
Super User
Posts: 7,401

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: 6,939

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
Respected Advisor
Posts: 4,649

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
Respected Advisor
Posts: 3,893

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;
      h1.add();
    end;
run;
Valued Guide
Posts: 797

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;
h1.add();
end;
Ask a Question
Discussion stats
  • 5 replies
  • 187 views
  • 3 likes
  • 6 in conversation