topic Re: sequence in SAS Programming

sequence

molla — Wed, 23 Nov 2016 12:50:32 GMT

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

Re: sequence

RW9 — Wed, 23 Nov 2016 12:56:47 GMT

Sort your data by ID, then do:

data want;
  set have;
  by id;
  retain seq;
  seq=ifn(first.id,sum(seq,1),seq);
run;

Re: sequence

Kurt_Bremser — Wed, 23 Nov 2016 12:58:47 GMT

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;

Re: sequence

PGStats — Wed, 23 Nov 2016 22:25:48 GMT

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;

Re: sequence

Patrick — Wed, 23 Nov 2016 23:17:26 GMT

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;

Re: sequence

mkeintz — Thu, 24 Nov 2016 01:19:36 GMT

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;