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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.