Overview

This SAS note explains how SAS Visual Analytics imports columns that contain missing values. The behavior depends on the source of the data and which release of SAS Visual Analytics you are using.

Microsoft Excel Files

When you import a Microsoft Excel file, numeric columns that contain blanks might be imported as character columns. This is because SAS^® identifies blanks as missing character values. Here are two workarounds:

• Convert the file to CSV. If you choose to convert the file to CSV, then review the next sections to understand how your release of SAS Visual Analytics handles missing values in CSV files.
• In Microsoft Excel, select each cell with a missing value. On the Home tab, in the Editing group, click the arrow next to the Clear button and select Clear All.

CSV Files

When you import a CSV file, numeric columns that contain missing values are handled differently depending on the release of SAS Visual Analytics.

Release 7.2 (SAS 9.4_M2) or an earlier release

If you are using 7.2 (SAS 9.4_M2), or any earlier release, then numeric columns with missing values are imported as character columns if the missing values are contained within quotation marks. In the following example, the Employee Number column is imported as character because it contains missing values contained within quotation marks (".") for both Bob and Linda.

To resolve this issue, remove the quotation marks surrounding the missing values before importing. So, the modified file might look like this:

Release 7.2 (SAS 9.4_M3) through release 7.5

If you are using release 7.2 (SAS 9.4_M3), 7.3, 7.4 or 7.5, then numeric columns with missing values are correctly imported as numeric if the missing values are represented in either of the forms shown in the previous two examples.

In addition, the missing values can be represented by no value. In the following example, two consecutive commas indicate that there is no value for Employee Number.

Release 8.1 and later

If you are using 8.1 or later, then the missing values must be completely missing. In the following example, two consecutive commas indicate that there is no value for Employee Number.

Operating System and Release Information