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 |
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
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;
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
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
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
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
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
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
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
Thank you very much Ksharp Its working as per the requirement.
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.