Character and Numeric in Same Field

[gbobbie]

Hello.  I am fairly new to SAS and have a customer that wants both text and numbers in the same field. 

Example:

Field1

N/A

0.03

0.04

0.05

N/A

From my SQL experience, this is not possible and would need to be converted after being exported to Excel.  However, being that I'm new to SAS I wasn't sure if there were any programming tips that could help me here, to convert before export.  Thanks in advance!

[PaigeMiller · Accepted solution]

SAS numeric variables can contain only actual numbers or a missing value indicator, which is usually a dot. This should export to Excel as an cell with a dot. Numeric variables cannot contain text.

You can create a custom format to have the dot shown as any text string you want.

proc format;
	value yf .='N/A';
run;

/* Create phony data */
data a;
	y=.; /* Missing value is a dot */
	output;
	y=7; /* Non-missing value */
	output;
	format y yf.;
run;

ods excel file="test.xlsx";
proc print data=a; run;
run;
ods excel close;  

[gbobbie]

Thank you!

[ballardw]

Values no. However you can have special missing values assigned to such things as "N/A" and provide a custom format to display text for those values.

If you are reading from an external text file you can even read such text into either specific numeric or special missing values using a custom informat.

The following is a brief example of reading such data with a custom informat and displaying with a matching format.

proc format;
invalue myval (Upcase)
'N/A' = .N
'UNK' = .U
;

value myval
.N = 'N/A'
.U = 'Unknown'
other= [best8.]
;

data example;
   input x :myval. y;
datalines ;
1    1
n/a  n/a
.2345 .2345
UNK  UNK
134.5678 134.5678
;

proc print data=example;
   format x myval.;
run;

INVALUE creates a custom informat to read values. In this case the text reads into two special missing values, .N and .U.

There are a possible 27 special missing values .A through .Z (case is irrelevant) and ._  . These value would not be used in any arithmetic processing and by default in a table view or print output without a custom format display the letter or _ character, N and U in the example. The custom format allows you to display a meaningful value. If you permanently associate the format with the variable then the custom value will appear as long as the format is available in the current SAS session.

The option OTHER in the format says "display any value not specifically described elsewhere with the format that appears in the brackets".

The invalue uses the option UPCASE to convert the values read to uppercase before comparing to the list of values. So "n/a" "N/A" "N/a" and "n/A" would all be treated the same, which can be helpful if you people entering values and sometimes get capitalization wrong,

The format would have to be permanently assigned to the variable to have the formatted value appear in export and can still have issues depending on exactly which method you use to "export" data.