BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
vraj1
Quartz | Level 8

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 ?

 

1 ACCEPTED SOLUTION

Accepted Solutions
hashman
Ammonite | Level 13

@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. 

    
   

View solution in original post

9 REPLIES 9
Kurt_Bremser
Super User

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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...

 

AshleyBright
Obsidian | Level 7

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

ballardw
Super User

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

AshleyBright
Obsidian | Level 7

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

AshleyBright
Obsidian | Level 7

Hi @vraj1

 

Did you manage to find a solution for this problem?

AshleyBright
Obsidian | Level 7

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.

Kurt_Bremser
Super User

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.

hashman
Ammonite | Level 13

@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. 

    
   

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 2353 views
  • 1 like
  • 6 in conversation