Remove single or double quotes around values

[SASPhile]

A variable has below values coming from excel and SAS datasets will have the values as below

Credit

Debit

"No Value"

'No Value'

" "

' '

In the above test data, how to convert "No Value", 'No Value', " ", ' ' to without quotes as below. Two empty rows will be there in the last two records.

Credit

Debit

No Value

No Value

[Reeza · Accepted solution]

DEQUOTE() function.

---

@SASPhile wrote:

A variable has below values coming from excel and SAS datasets will have the values as below

Credit

Debit

"No Value"

'No Value'

" "

' '

In the above test data, how to convert "No Value", 'No Value', " ", ' ' to without quotes as below. Two empty rows will be there in the last two records.

Credit

Debit

No Value

No Value

---

[Tom]

How are you reading the values from Excel?  Are you using PROC IMPORT?  The XLSX libname engine? Dumping the file to a CSV file and then reading that file?

If you are reading the data from a CSV (or other delimited text file) then using the DSD option on the INFILE statement will automatically remove the quotes.

Otherwise use the DEQUOTE() function.

[LeonidBatkhan]

I would caution about using DEQUOTE() function since it does not exactly remove "surrounding" quotes. According to the documentation for DEQUOTE function it "Removes matching quotation marks from a character string that begins with a quotation mark, and deletes all characters to the right of the closing quotation mark."

That means that if your character string begins with a quotation mark and has the same quotation mark somewhere inside it's value, the DEQUOTE function will strip all the characters after that "internal" quotation mark.

Try running the following code:

data A;
   length c $40;
   input c $char40.;
   c1 = dequote(c);
   put c1=;
   datalines;
'Some' value'
;

It will produce the following in the SAS log:

c1=Some

That is you will lose information (word value is lost).

UNQUOTE() Function

To prevent such information loss I developed and implemented a user-defined function UNQUOTE that does exactly what was asked in this question - remove matching single or double quotes surrounding character value.  You can find the UNQUOTE() function implementation code along with a detail review of other quoting and unquoting issues in the following blog post How to unquote SAS character variable values.