How to compress around a comma inside a string

[arde]

Hi.  

I am trying to deleted spaces around commas inside a singe character string:

Have

Mary Had , A Little , Lamb

Want

Mary Had,A Little,Lamb

Any help is truly appreciated!

[maguiremq · Accepted solution]

I would use PRXCHANGE, but I'm sure there's a different function that could do it as well.

data want;
var = "Mary Had, A Little,Lamb";
var_want = prxchange("s/(\s\,)|(\,\s)/,/", -1, var);
run;

Basically saying, substitute a space followed by a comma OR a comma followed by a space, replace it with a single comma. -1 tells SAS to do the replacement for the entire field.

Not sure if this will apply to all cases, though - especially if you have multiple spaces. Then you will need to modify the \s. 

[arde]

Thank you!

[Tom]

The example data works fine with just TRANWRD() function.

want = tranwrd(have,' , ',',');

If the real data has multiple adjacent spaces you can try using COMPBL() first to collapse all places with multiple spaces into a single space.

If the real data has some commas with only leading or only trailing spaces then you might need to work a little harder.

data have;
  input string $50.;
cards;
Mary Had , A Little , Lamb
Mary Had  ,  A Little  ,  Lamb
Mary Had, A Little ,Lamb
Mary Had  ,A Little,  Lamb
; 

data want;
  set have;
  length newstr $50;
  newstr=tranwrd(tranwrd(compbl(string),', ',','),' ,',',');
run;

Result

Obs                string                        newstr

 1     Mary Had , A Little , Lamb        Mary Had,A Little,Lamb
 2     Mary Had  ,  A Little  ,  Lamb    Mary Had,A Little,Lamb
 3     Mary Had, A Little ,Lamb          Mary Had,A Little,Lamb
 4     Mary Had  ,A Little,  Lamb        Mary Had,A Little,Lamb

[arde]

that's good to know, thank you