topic Re: Reg:Clustering if obs is same new unique number to generate in SAS Procedures

Reg:Clustering if obs is same new unique number to generate

R_Win — Tue, 26 Apr 2011 14:15:15 GMT

Hi

Iam having acc_no with same number now i want a new variable as seq_no it should be same for all the accouns that are same:

data new;
input acc_no amt;
cards;
1012 23
1013 45
1012 56
1012 56
1013 80
1023 42
run;

output:

acc_no amt seq_no
1012 23 1
1013 45 2
1012 56 1
1012 56 1
1013 80 2
1023 42 3

For this the acc no 1012 is having 1 as seq_no ,and 1013 has 2 as seq_no like that.

Re: Reg:Clustering if obs is same new unique number to generate

SPR — Tue, 26 Apr 2011 14:49:28 GMT

Hello R_Win,

This is a solution:
[pre]
data i;
input acc_no amt;
i+1;
cards;
1012 23
1013 45
1012 56
1012 56
1013 80
1023 42
run;
proc sort data=i;
by acc_no;
run;
data r;
set i;
if First.acc_no then seq_no+1;
by acc_no;
run;
proc sort data=r out=r(drop=i);
by i;
run;
[/pre]
If it is not necessary to keep the same order of observations in the input and output datasets then variable i can be deleted along side with the last sorting.
Sincerely,
SPR

Re: Reg:Clustering if obs is same new unique number to generate

Ksharp — Wed, 27 Apr 2011 01:47:10 GMT

If you care the origin order of the dataset. Then try to use Hash Table.
[pre]
data new;
input acc_no amt;
cards;
1012 23
1013 45
1012 56
1012 56
1013 80
1023 42
;
run;
data new(drop=rc);
declare hash ha(hashexp:10);
ha.definekey('acc_no');
ha.definedata('seq_no');
ha.definedone();

do until(last);
set new end=last;
rc=ha.find();
if rc ne 0 then seq_no+1;
rc=ha.add();
output;
end;
stop;
run;
[/pre]

Ksharp