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
Sort your data by ID, then do:
data want; set have; by id; retain seq; seq=ifn(first.id,sum(seq,1),seq); run;
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.