I have a dataset that looks like this
data temp;
input Inv_Code $20.;
datalines;
INV001
INV002
964987
789469
I-87700
776012
FM073026-1
14774
16316/1
27107
56549
001INV
210677
70172
FM0668#8-1
11459/1
1/61361
;
run;
I need to identify duplicates as under :
INV001
001INV
789469
etc so on and so forth
One approach would assign each character to a separate variable:
data all_characters;
set have;
array letters {20} $ 1 C1-C20;
do _n_=1 to 20;
letters{_n_} = substr(inv_code, _n_, 1);
end;
call sortc (of C1-C20);
newkey = cat(of C1-C20);
run;
By sorting the characters, it becomes relatively easy to compare for the same set of characters:
proc sort data=all_characters;
by newkey;
run;
data want;
set all_characters;
by newkey;
if last.newkey=0 or first.newkey=0;
run;
Are you considering INV001 and 001INV as similar string
964987 and 789469 as similar string.
What is the basis to consider two string duplicate?
Someone may have incorrectly written it but it would have all the caracters from the the previous one. Like 78956 to 65987 and so on.
it just may be reordered thats all
One approach would assign each character to a separate variable:
data all_characters;
set have;
array letters {20} $ 1 C1-C20;
do _n_=1 to 20;
letters{_n_} = substr(inv_code, _n_, 1);
end;
call sortc (of C1-C20);
newkey = cat(of C1-C20);
run;
By sorting the characters, it becomes relatively easy to compare for the same set of characters:
proc sort data=all_characters;
by newkey;
run;
data want;
set all_characters;
by newkey;
if last.newkey=0 or first.newkey=0;
run;
Thanks for the logic 🙂
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 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.