topic Re: how to remove hidden quotes from data in SAS Programming

how to remove hidden quotes from data

vraj1 — Fri, 01 Jun 2018 09:01:58 GMT

In my data for a variable if i see it looks fine like

ex - stratos

if i copy it and check in notepad it becomes "startos" masked with quotes this is becoming problem if i merge data on

How to remove these hidden quotes ?

Re: how to remove hidden quotes from data

Kurt_Bremser — Fri, 01 Jun 2018 09:36:12 GMT

Please use the macro from my second footnote to convert the relevant portion of your dataset to a data step and post the resulting code here as advised in my third footnote. Only with having a clear picture of your data will we be able to provide help.

Re: how to remove hidden quotes from data

RW9 — Fri, 01 Jun 2018 10:29:46 GMT

Use compress(). Also, a quick search on here will reveal several topics with answers already:

https://communities.sas.com/t5/SAS-Procedures/SAS8-How-to-remove-special-characters-from-string/td-p/59186

Re: how to remove hidden quotes from data

ballardw — Fri, 01 Jun 2018 14:37:35 GMT

And how to you "copy it and check in notepad"? Export the data to a text file? Copy from Viewtable view?

Re: how to remove hidden quotes from data

AshleyBright — Tue, 22 Jan 2019 19:10:38 GMT

Hi @vraj1

Did you manage to find a solution for this problem?

Re: how to remove hidden quotes from data

AshleyBright — Tue, 22 Jan 2019 19:11:42 GMT

@RW9 Compress is not helping in this case! Is there any other way?

Re: how to remove hidden quotes from data

AshleyBright — Tue, 22 Jan 2019 19:14:07 GMT

@ballardw I am just copying the text from the cell to notepad and I am seeing those extra quotes.

Re: how to remove hidden quotes from data

AshleyBright — Tue, 22 Jan 2019 19:38:33 GMT

When I copy the text to notepad it's like this:

"""test:{""""text"""":""""test/5.0 (werrwe VV 1.0; Qwe77; y99) TestTestTest/123.89 (ABCD"""

But when see it in the dataset it's like this:

"test:{""text"":""test/5.0 (werrwe VV 1.0; Qwe77; y99) TestTestTest/123.89 (ABCD"

I tried dequote, compress functions it didn't work. Any help is much appreciated.

Re: how to remove hidden quotes from data

Kurt_Bremser — Tue, 22 Jan 2019 20:29:04 GMT

This question is half a year old, and only the original participants can see that you posted something.

Post your question in a new thread, and include example data and your code, so it's easier for us to help you.

Re: how to remove hidden quotes from data

hashman — Tue, 22 Jan 2019 21:21:08 GMT

@AshleyBright:

Forget Notepad. When you copy-and-paste into it, what you see may not be what you have in your SAS variable.

Instead, locate the observation number with the variable value you want to investigate. Suppose this number is 123, your variable in question is called S, and the data set is called HAVE. Run the step:

data _null_ ;               
  obs_num = 123 ;             
  set have point = obs_num ;
  do pos = 1 to length (s) ;
    b = char (s, pos) ;     
    put b=$hex2. b= pos= ;  
  end ;                     
  stop ;                    
run ;

and look at the log. For the first 14 positions of the string you've exemplified here, the step will print:

b=22 b=" pos=1
b=22 b=" pos=2
b=22 b=" pos=3
b=74 b=t pos=4
b=65 b=e pos=5
b=73 b=s pos=6
b=74 b=t pos=7
b=3A b=: pos=8
b=7B b={ pos=9
b=22 b=" pos=10
b=22 b=" pos=11
b=22 b=" pos=12
b=22 b=" pos=13
b=74 b=t pos=14

This will represent the true character content of your string, and you'll be able to determine whether the "quotes" you see in the viewer are really quotes or something else and/or if there're some "hidden" characters you can't see. If some characters that look to you like quotes are in reality other characters, such as not-printables, note their hex values in the log and use them as hex literals with the COMPRESS function to kill them.

Now if you determine from the log that they are real double quotes, only multiplied for some reason, and you want to collapse any number of consecutive double-quotes to a single double-quote, run:

data want ;                     
  set have ;                    
  do while (find (s, '""')) ;   
    s = tranwrd (s, '""', '"') ;
  end ;                         
run ;

Or, if you want to leave no more than 2 consecutive double-quotes in the value of your variable, change the DO loop to:

  do while (find (s, '"""')) ;    
    s = tranwrd (s, '"""', '""') ;
  end ;

Anything is possible if you really know what's in your string and what and in which manner you want to change (as long as there's a pattern). But looking at the SAS data after moving them out of the SAS system, especially by copying and pasting, is error-prone.

Kind regards

Paul D.