topic Re: Removing repeated substring in String/Observation from the string in SAS Programming

Removing repeated substring in String/Observation from the string

keen_sas — Tue, 12 Mar 2019 13:41:35 GMT

Hi ,

I have repeated sub strings/duplicate words in a string . I would like to remove the repeated words from the string and
keep only the unique values in the string.Words are mix of cases. Any shortcut approach to do this. i am trying to do this using arrays and do loop .

Have	Want
apple orange kiwi apple grapes strawberry peach kiwi peach	apple orange kiwi grapes strawberry peach
USA UK Australia Japan USA UK	USA UK Australia Japan
FOOTBALL BasketBall basketball Hockey football	Football basketball hockey

Re: Removing repeated substring in String/Observation from the string

Ksharp — Tue, 12 Mar 2019 14:07:17 GMT

data have;
input x $80.;
cards;
apple orange kiwi apple grapes strawberry peach kiwi peach
USA UK Australia Japan USA UK
FOOTBALL BasketBall basketball Hockey football 
;
run;
data want;
 set have;
 array a{999} $ 100;
 n=0;
 do i=1 to countw(x,' ');
  temp=lowcase(scan(x,i,' '));
  if temp not in a then do;	n+1;a{n}=temp;end;
 end;
 want=catx(' ',of a{*});
 drop n i a: temp;
run;

Re: Removing repeated substring in String/Observation from the string

PeterClemmensen — Tue, 12 Mar 2019 15:32:34 GMT

data have;
input string :$200.;
infile datalines dlm=',';
datalines;
apple orange kiwi apple grapes strawberry peach kiwi peach
USA UK Australia Japan USA UK
FOOTBALL BasketBall basketball Hockey football
;

data want(keep=string newstring);
   set have;
   newstring=scan(string, 1, ' ');
   do i=2 to countw(string,' ');
      word=scan(string, i, ' ');
      found=find(newstring, word, 'it');
      if found=0 then newstring=catx(' ', newstring, word);
   end;
run;

proc print data=want;
run;

This gives you

Re: Removing repeated substring in String/Observation from the string

PGStats — Tue, 12 Mar 2019 21:53:45 GMT

With perl regular expression matching:

data have;
input str $80.;
cards;
apple orange kiwi apple grapes strawberry peach kiwi peach
USA UK Australia Japan USA UK
FOOTBALL BasketBall basketball Hockey football 
;

data want;
if not prxId then prxId + prxParse("s/\b(\w{2,})\b(.*)\b(\1\s*)\b/\1\2/io");
set have;
wantStr = str;
do i = 1 to 100 until (times=0);
    call prxChange(prxId,1,wantStr,wantStr,len,trunc,times);
    end;
drop i prxId len trunc times;
run;

proc print; run;

         Obs    str

          1     apple orange kiwi apple grapes strawberry peach kiwi peach
          2     USA UK Australia Japan USA UK
          3     FOOTBALL BasketBall basketball Hockey football

         Obs    wantStr

          1     apple orange kiwi grapes strawberry peach
          2     USA UK Australia Japan
          3     FOOTBALL BasketBall Hockey

Re: Removing repeated substring in String/Observation from the string

Ksharp — Wed, 13 Mar 2019 12:08:59 GMT

I think you should use FINDW(), would be better.

Re: Removing repeated substring in String/Observation from the string

keen_sas — Wed, 13 Mar 2019 13:19:39 GMT

Thank you all for your responses, gained some new approach of programming.

Re: Removing repeated substring in String/Observation from the string

Amir — Wed, 13 Mar 2019 17:32:08 GMT

You asked an interesting question and got some interesting answers.

You can help others further by marking one of the provided answers as the solution (not this one).

Regards,

Amir.

Re: Removing repeated substring in String/Observation from the string

dee7 — Thu, 20 Jun 2019 15:12:21 GMT

I am hoping someone can help with this. I have a similar problem and the code posted her worked but I have over 20 variables with this sort of repeated strings. Do I have to do this over and over for all 20+ variables? Does anyone know any shorter way to do this for multiple variables at once? Can we use this in an array to achieve the same result for many variables?

Re: Removing repeated substring in String/Observation from the string

Amir — Fri, 21 Jun 2019 11:29:46 GMT

@dee7 Welcome to SAS Communities. I recommend you ask a new question, supplying the data you have and the data you want in the form of SAS data steps using the datalines statement to supply the data in each case. More people will see the question and so it should get answered more quickly and with a wider range of solutions.

I expect an answer could involve using one of the dictionary tables and macro variables.

Feel free to provide a link to this question also, if desired, but all necessary information should ideally be in your question.

Regards,

Amir.

Re: Removing repeated substring in String/Observation from the string

keen_sas — Tue, 07 Jan 2020 13:24:05 GMT

The below code is sometimes excluding the substring with a minor difference in the spelling . Tried with both FIND and FINDW but it is not excluding the exact matching word. In the below example Japan and Japanese are two different words but when using find function JAPAN is getting excluded because of JAPANESE , similarly in the last row when both FATESTCD and FATEST are present only FATESTCD is present , FATEST is getting excluded. Only the exact word should get removed ,but its not happening here.Any suggestions/updates on this.

How can we try this using PRXMATCH,?

data have;
input string :$200.;
infile datalines dlm=',';
datalines;
apple orange kiwi apple grapes strawberry peach kiwi peach
China USA UK Australia Japanese USA UK Australian Japan Chinase
FOOTBALL BasketBall basketball Hockey football
FACAT FATESTCD FATEST FAOBJ STDT STDTC VISIT VISITNUM
;

data want(keep=string newstring);
   set have;
   newstring=scan(string, 1, ' ');
   do i=2 to countw(string,' ');
      word=scan(string, i, ' ');
      found=find(newstring, word, 'it');
      *found=findw(newstring, word, 'it');;
      if found=0 then newstring=catx(' ', newstring, word);
   end;
run;