Say you have dataset with a column, we will call it ‘myVals’, with values (1, 2, 3 ,T ,N).  You want to be able to run summary statistics on ‘myVals’ easily, say in SAS Enterprise Guide, but the character values get in the way since the column is formatted as characters.

The quickest way to convert the data (ignoring the T and N values) is to simply change the select statement for the data to have the myVals field processed with the INPUT function like so:

SELECT
INPUT(myVals,BEST2.) as myVals
FROM ...

or (to preserve the character values) use:

(CASE WHEN 'T' = myVals THEN INPUT('.T',BEST2.) ELSE INPUT(myVals,BEST2.) END) as myVals

If you convert the ‘T’ and ‘N’ values to ‘.T’ and ‘.N’ then we are allowed to convert the column to numeric format in SAS.  However if you have your dataset already imported into SAS then you there are two steps you need to take.  First run a simple select query on the dataset, and create a ‘computed column’ that will recode the ‘T’ and ‘N’ values to ‘.T’ and ‘.N’,  The code for this will look like this:

(CASE
WHEN 'T' = myVals THEN '.T'
WHEN 'N' = myVals THEN '.N'
ELSE myVals
END) FORMAT=$CHAR2. AS new_myVals

The new_myVals column is still in character format at this point, so we run a second query (I know of no way to do this all in a single query) where we will now convert the new_myVals column to numeric format using:

INPUT(new_myVals, BEST2.)

NOTE: I tried running the CASE statement and then the INPUT function after it in the same query, but the INPUT function does not seem to work on calculated columns; so that is why we must create a new dataset first with the .T and .N values and then the INPUT function will work on the new (numeric friendly format) column values.  Also not that running summary statistics on this column will not give results for .T and .N values.  They remain in the dataset, however you would need to change them to numbers if you wanted to run simple summary statistics on them.

BEWARE: If you export your SAS dataset with .T and .N values stored in your new numeric formatted column using the simple Excel export, these values WILL NOT be sent to the Excel document.  They will simply be treated as blanks or empty values.  Thus, all the more reason to convert the character values to numbers.  HOWEVER, if you export the .T and .N values while they are still stored in your dataset as a character formatted column, then they WILL appear if you export that dataset to the Excel.  This is one of the problems of exporting data (and importing data between software programs)  because data me lost, unbeknownst to the person performing the export.