topic Re: Assigning Increment number values for duplicate values in SAS Procedures

Assigning Increment number values for duplicate values

1239 — Fri, 06 Apr 2012 05:03:10 GMT

Hi,

I have dataset as shown below. My requirement is to assign the incrementing number values for duplicate values of that number of times. If it is unique then same value should be retained. Please help me.

--dataset structure

Name	Result
A	aa
B	aa
C	bb
D	cc
E	cc
F	cc
G	dd
H	dd
I	dd
J	dd
K	dd
L	dd

--desired output

Name	Result
A	aa1
B	aa2
C	bb
D	cc1
E	cc2
F	cc3
G	dd1
H	dd2
I	dd3
J	dd4
K	dd5
L	dd6

Re: Assigning Increment number values for duplicate values

manojinpec — Fri, 06 Apr 2012 08:12:21 GMT

I have written followin but don't know why it is not working.May be someone can help;-

data have;

input a $ b $;

cards;

A aa

B aa

C bb

D cc

E cc

F cc

G dd

H dd

I dd

J dd

K dd

L dd

;

run;

data want;

set have;

by b;

retain count 0;

if first.b ne last.b then do; count=count+1; end;

else count=0;

run;

Re: Assigning Increment number values for duplicate values

Linlin — Fri, 06 Apr 2012 12:38:54 GMT

try this one:

options nocenter;

data have;

input (name result)($);

cards;

A aa

B aa

C bb

D cc

E cc

F cc

G dd

H dd

I dd

J dd

K dd

L dd

;

data want (rename=new=result);

length new $ 8;

set have;

by result;

if first.result then count=0;

count+1;

new=cats(result,count);

drop result count;

run;

proc print;run;

Obs result name

1 aa1 A

2 aa2 B

3 bb1 C

4 cc1 D

5 cc2 E

6 cc3 F

7 dd1 G

8 dd2 H

9 dd3 I

10 dd4 J

11 dd5 K

12 dd6 L

Linlin

Re: Assigning Increment number values for duplicate values

Haikuo — Fri, 06 Apr 2012 13:01:22 GMT

With help of DOW-loop, a slightly simplified version:

data want ;

do _n_=1 by 1 until (last.result);

set have;

by result;

result=cats(result,_n_);

output;

end;

run;

Regards,

Haikuo

Re: Assigning Increment number values for duplicate values

Ksharp — Mon, 09 Apr 2012 02:14:16 GMT

I noticed some special thing 'bb' doesn't change.it is what you need?

data have;
input name $ result $;
cards;
A     aa
B     aa
C     bb
D     cc
E     cc
F     cc
G     dd
H     dd
I     dd
J     dd
K     dd
L     dd
;
run;
data want(drop=result n);
 set have;
 by result notsorted;
 length _result $ 20 ;
 if first.result then n=0;
 n+1; _result=cats(result,n);
 if first.result and last.result then _result=result;
run;

Ksharp

Re: Assigning Increment number values for duplicate values

1239 — Tue, 10 Apr 2012 07:29:54 GMT

Hi,

Thanks a lot for the code and its working as per the requirement. Now there is slight a change to the requirement that is first repeating value should be retained as such and subsequent duplicate values to be incremented accordingly.

I have tweaked the code and getting the desired output but my doubt is...is my code can be written in efficient way?

Thanks in advance.

data have;

input name $ result $;

cards;

A aa

B aa

C bb

D cc

E cc

F cc

G dd

H dd

I dd

J dd

K dd

L dd

;

run;

data want(drop=result n);

set have;

by result notsorted;

length _result $ 20;

if first.result = 0 then n=0;

n+1;

_result=cats(result,n);

if first.result = 1 then _result=result;

run;

--desired output

name _result

A aa

B aa1

C bb

D cc

E cc1

F cc2

G dd

H dd1

I dd2

J dd3

K dd4

L dd5

Re: Assigning Increment number values for duplicate values

Ksharp — Tue, 10 Apr 2012 07:53:32 GMT

Try it.

data have;
input name $ result $;
cards;
A     aa
B     aa
C     bb
D     cc
E     cc
F     cc
G     dd
H     dd
I     dd
J     dd
K     dd
L     dd
;
run;
data want(drop=result);
 set have;
 by result notsorted;
 length _result $ 20 ;
 _result=cats(result,'1');
 if first.result then _result=result;
run;

Ksharp

Re: Assigning Increment number values for duplicate values

1239 — Tue, 10 Apr 2012 08:19:21 GMT

Hi Ksharp,

Sorry I updated the wrong desired output. I have updated it now correctly.

--desired output

name _result

A aa

B aa1

C bb

D cc

E cc1

F cc2

G dd

H dd1

I dd2

J dd3

K dd4

L dd5

Re: Assigning Increment number values for duplicate values

Ksharp — Tue, 10 Apr 2012 08:26:24 GMT

OK.

data have;
input name $ result $;
cards;
A     aa
B     aa
C     bb
D     cc
E     cc
F     cc
G     dd
H     dd
I     dd
J     dd
K     dd
L     dd
;
run;
data want(drop=result n);
 set have;
 by result notsorted;
 length _result $ 20 ;
 n+1;_result=cats(result,n);
 if first.result then do;_result=result; n=0;end;
run;

Ksharp

Re: Assigning Increment number values for duplicate values

1239 — Tue, 10 Apr 2012 09:20:54 GMT

Thank you very much Ksharp Its working as per the requirement.

Re: Assigning Increment number values for duplicate values

Haikuo — Tue, 10 Apr 2012 12:36:43 GMT

There is DOW version as well:

data have;

input name $ result $;

cards;

A aa

B aa

C bb

D cc

E cc

F cc

G dd

H dd

I dd

J dd

K dd

L dd

;

run;

data want;

do _n_=0 by 1 until (last.result);

set have;

by result;

if _n_>0 then result=cats(result,_n_);

output;

end;

run;

proc print;run;

Haikuo