BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
1239
Calcite | Level 5

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

NameResult
Aaa
Baa
Cbb
Dcc
Ecc
Fcc
Gdd
Hdd
Idd
Jdd
Kdd
Ldd

--desired output

NameResult
Aaa1
Baa2
Cbb
Dcc1
Ecc2
Fcc3
Gdd1
Hdd2
Idd3
Jdd4
Kdd5
Ldd6
1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

View solution in original post

10 REPLIES 10
manojinpec
Obsidian | Level 7

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;

Linlin
Lapis Lazuli | Level 10


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

Haikuo
Onyx | Level 15

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

Ksharp
Super User

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

1239
Calcite | Level 5

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

Ksharp
Super User

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

1239
Calcite | Level 5

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

Ksharp
Super User

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

1239
Calcite | Level 5

Thank you very much KsharpSmiley Happy Its working as per the requirement.

Haikuo
Onyx | Level 15

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

sas-innovate-2024.png

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.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 6959 views
  • 1 like
  • 5 in conversation