topic Re: How to remove brackets and quotes from observations in a dataset in SAS Programming

How to remove brackets and quotes from observations in a dataset

cfun — Fri, 09 Apr 2021 17:20:32 GMT

Hello all,

I have a list of artists that is formatted like so:

['Justin Bieber']

['Brockhampton']

etc

and I want to make it so these variables no longer have quotes or brackets and instead look like:

Justin Bieber

Brockhampton

How would I do this?

Re: How to remove brackets and quotes from observations in a dataset

cfun — Fri, 09 Apr 2021 17:25:42 GMT

Additionally, this dataset contains many other variables, but I am fine with how they are formatted.

Re: How to remove brackets and quotes from observations in a dataset

Reeza — Fri, 09 Apr 2021 17:25:44 GMT

COMPRESS()

x = compress(originalVar, "[]'");

Note that the double quotes contain both brackets and single quote.

@cfun wrote:

Hello all,

I have a list of artists that is formatted like so:

['Justin Bieber']

['Brockhampton']

etc

and I want to make it so these variables no longer have quotes or brackets and instead look like:

Justin Bieber

Brockhampton

How would I do this?

Re: How to remove brackets and quotes from observations in a dataset

LeonidBatkhan — Fri, 09 Apr 2021 19:06:04 GMT

Using compress() function as suggested by Reeza may have unintended consequences. For example, if you have legitimate single quotes in a name, such as ['O'Connor'], compress will remove ' after O as well. A cleaner way would be

NAME = transtrn(NAME,"['",trimn(''));
NAME = transtrn(NAME,"']",trimn(''));

This will selectively remove substring

['

and substring

']

For more information on this technique, see Deleting a substring from a SAS string.

Hope this helps.

Re: How to remove brackets and quotes from observations in a dataset

Tom — Fri, 09 Apr 2021 19:16:10 GMT

Depends on whether those characters every appear in the actual data or not.

If not then it is pretty simple. So if your existing datasets is named HAVE and the existing variable is named VAR then you could use COMPRESS() to remove those three characters.

data want;
  set have;
  var = compress(var,"[']");
run;

Or you could use SCAN() .

  var = scan(var,1,"[']");

If they appear in the data and the quotes are properly escaped then you might want to use SCAN() and DEQUOTE().

  var = dequote(scan(var,1,"[]",'q'));

If the embedded single quotes are "escaped" with \ instead of being doubled up then use:

  var = dequote(scan(tranwrd(var,"\'","''"),1,"[]",'q'));

If they are not protected in anyway then perhaps you just want to remove the first 2 and last 2 characters.

 var = substrn(var,3,length(var)-4);

Re: How to remove brackets and quotes from observations in a dataset

Ksharp — Sat, 10 Apr 2021 10:23:18 GMT

Couldl be simpler.

data have;
input str $20.;
cards;
['Justin Bieber']
['Brockhampton']
['O'Connor']
;

data want;
 set have;
 want=prxchange("s/^\['|'\]$//",-1,strip(str));
 run;

Re: How to remove brackets and quotes from observations in a dataset

Patrick — Sun, 11 Apr 2021 06:49:29 GMT

As a variant to the RegEx @Ksharp posted below RegEx will only remove ['....'] if it encloses the string and both parts exist.

data have;
  input str $20.;
  cards;
['Justin Bieber']
['Brockhampton']
['O'Connor']
['O'['Con']nor']
['O'['Connor']
['O'Con']nor']
['O'Connor
O'Connor']
O'['Con']nor
;

data want;
  set have;
  length new_str $20;
  new_str=prxchange("s/^\['(.*)'\]/$1/oi",1,strip(str));
run;

Re: How to remove brackets and quotes from observations in a dataset

andreas_lds — Mon, 12 Apr 2021 06:50:09 GMT

I am regex-fanboy, but in this case i don't see the need to use a regex.

The first and last two chars should be removed, so why not just substr:

clean = substr(str, 3, lengthn(str) - 4);

Re: How to remove brackets and quotes from observations in a dataset

Sri4_ — Wed, 29 Sep 2021 05:58:07 GMT

How to remove brackets and quotes from observations in a dataset.

in my data having values 22.9() and 1.9(mg/dl) like this but i want to remove () without removing this brackets 1.9mg/dl

Re: How to remove brackets and quotes from observations in a dataset

Patrick — Wed, 29 Sep 2021 06:42:55 GMT

@Sri4_ Please as this as a NEW question and don't just post into the middle of an "old" discussion that unfortunately never got closed.

You can always reference another discussion in your NEW question.