topic Re: Remove extra spaces and combine letters in SAS Programming

Remove extra spaces and combine letters

finans_sas — Tue, 29 Jun 2021 20:56:41 GMT

Hello Everyone:

I have a column with names, but there are extra spaces in the names. I provided a sample dataset below. My goal is to remove the space between single letters and combine them into one word. However, I do not want to merge the single-letters with multi-letter words. Is it possible to write a script such that if it will search in a string and stop where a multi-letter word first appears (for example, the word Supply in the first case) and it will merge all the letters to the left of it? I would really appreciate your help with step.

Thank you so much

Actual	Desired
A B Supply Inc	AB Supply Inc
A B C Machines Inc	ABC Machines Inc
A B C D Operations Inc	ABCD Operations Inc
A B C D E US Inc	ABCDE US Inc

Re: Remove extra spaces and combine letters

ChrisNZ — Tue, 29 Jun 2021 22:30:22 GMT

This assumes all the singles letters come first as per your data.

We find the first set of 2 consecutive letters and compress what comes before:

POS    = prxmatch('/[a-z]{2}/i',STR); * The final i makes this match case-insensitive;
NEWSTR = compress(substr(STR,1,POS-2)) || ' ' || substr(STR,POS) ;

Re: Remove extra spaces and combine letters

andreas_lds — Wed, 30 Jun 2021 06:24:38 GMT

It should be possible to use a single regex with prxchange to get the required result, unfortunately those expressions need the "g" switch which is not supported. See https://stackoverflow.com/questions/4228750/removing-spaces-between-single-letters. Maybe someone else, with more regex experience can fix the problem. Using -1 in prxchange doesn't help because on subsequent iterations the first conditions is not fulfilled any more due to concatenations that took place.

Re: Remove extra spaces and combine letters

Patrick — Thu, 01 Jul 2021 10:10:05 GMT

@andreas_lds Not that I believe the data the OP posted are sufficiently representative but for what you mention and the sample data provided a look-ahead could do the job.

data have;
 infile datalines truncover;
 input str $40.;
 datalines;
A b c d e  
A B Supply Inc
A B C Machines Inc
A B C D Operations Inc
A B C D E US Inc
;

data want;
  set have;
  str=prxchange('s/(\w)\s+(?=\w(\s|$))/$1/oi',-1,strip(str));
run;

Re: Remove extra spaces and combine letters

Ksharp — Wed, 30 Jun 2021 13:24:40 GMT

data have;
 infile datalines truncover;
 input str $40.;
 datalines;
A b c d e  
A B Supply Inc
A B C Machines Inc
A B C D Operations Inc
A B C D E US Inc
;

data want;
  set have;
  length want_str $ 200;
  do i=1 to countw(str,' ');
    temp=scan(str,i,' ');
    if length(temp)=1 then want_str=cats(want_str,temp);
	 else want_str=catx(' ',want_str,temp);
  end;
run;

Re: Remove extra spaces and combine letters

finans_sas — Wed, 30 Jun 2021 18:45:31 GMT

Thank you so much, ChrisNZ for this code! I really appreciate it. When I used it, it was able to combine the letters the way I wanted, but it produces errors when I have names that do not need to be modified. For example, it converts "ABC Supply" to "ABCSupply ABC Supply" whereas the desired outcome here is the same as the actual outcome (ABC Supply). Is there a way to remedy this?

On a separate note, sometimes I have single letters in different parts of the names such as ABC U S Supply, which should be changed to ABC US Supply. However, there are not many of those.

Thank you so much!

Re: Remove extra spaces and combine letters

finans_sas — Wed, 30 Jun 2021 18:52:21 GMT

Thank you so much for your help, Patrick! I will try your suggestion.

Re: Remove extra spaces and combine letters

finans_sas — Wed, 30 Jun 2021 18:53:22 GMT

Thank you so much for your help, Ksharp! I will try your suggestion.