Overview

By default, each missing numeric value is represented in query results by a single period (.). This sample shows you how to use the PROC SQL CASE statement to define a data item expression that will return a 0 instead of a . for each missing value in the query results.

This sample uses output from SASHELP.SHOES, which has been edited to include missing values for Sales. Specifically, in all of the Regions, boots were discontinued and therefore Sales are now missing for that product.

Click the Full Code tab in this sample for code that generates the modified SHOES table. Click the Output tab in this sample to see the data in the modified table.

Change the Data Item Expression

1. Open the Expression Editor for the data item. In this sample, the data item is Sales.

   

2. Replace the Expression Text with the new expression. In this sample, the new expression is:

   case when (<MISSINGBOOTS.sales> eq .) then (<MISSINGBOOTS.sales> eq 0) else (<MISSINGBOOTS.sales>) end

3. Click OK to save the expression.
4. Test the query to see the following results. The missing Sales values now display as $0.

   

Additional Documentation

For a list of available documentation for SAS Information Map Studio, see http://support.sas.com/documentation/onlinedoc/ims/index.html.

For more information about how SAS handles missing values, see the documentation for Base SAS at http://support.sas.com/documentation/onlinedoc/base/index.html.

---

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

In the LIBNAME statment, replace '<your path>' with a valid path for your environment.

---

libname data '<your path>';

proc sort data=sashelp.shoes;
by Subsidiary;
run;

data data.missingboots;
set sashelp.shoes;
by Subsidiary;
where Region="United States";
if first.subsidiary then Sales=.;
run;

---

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

                                            The SAS System     15:34 Wednesday, September 10, 2008  44

  Obs     Region      Product         Subsidiary   Stores         Sales     Inventory       Returns

    1  United States  Boot            Chicago        16               .      $305,061        $3,735
    2  United States  Men's Casual    Chicago        26        $408,978      $831,672       $17,907
    3  United States  Men's Dress     Chicago        28        $261,607      $824,485        $8,915
    4  United States  Sandal          Chicago         2            $601       $17,121           $35
    5  United States  Slipper         Chicago        13        $329,235    $1,049,530       $10,654
    6  United States  Sport Shoe      Chicago        17         $17,347       $74,605          $684
    7  United States  Women's Casual  Chicago        11        $172,021      $474,524        $5,399
    8  United States  Women's Dress   Chicago        37        $293,313      $754,157       $10,194
    9  United States  Boot            Los Angeles    14               .      $347,252        $3,283
   10  United States  Men's Casual    Los Angeles     9        $177,010      $401,199        $6,336
   11  United States  Men's Dress     Los Angeles    15        $147,670      $348,869        $4,962
   12  United States  Sandal          Los Angeles     2            $737       $20,838           $38
   13  United States  Slipper         Los Angeles    11         $98,866      $330,743        $3,457
   14  United States  Sport Shoe      Los Angeles    19         $16,307       $98,221          $601
   15  United States  Women's Casual  Los Angeles     6         $62,661      $231,285        $2,346
   16  United States  Women's Dress   Los Angeles    16        $148,129      $452,383        $4,289
   17  United States  Boot            Minneapolis    16               .      $615,489        $4,475
   18  United States  Men's Casual    Minneapolis    16        $263,712      $564,741        $9,991
   19  United States  Men's Dress     Minneapolis    22        $235,842      $623,645        $7,344
   20  United States  Sandal          Minneapolis     8          $3,551       $32,907          $161
   21  United States  Slipper         Minneapolis    16        $131,813      $487,233        $4,833
   22  United States  Sport Shoe      Minneapolis    29         $18,151      $116,192        $1,009
   23  United States  Women's Casual  Minneapolis     7        $110,760      $287,412        $3,440
   24  United States  Women's Dress   Minneapolis    27        $224,168      $760,977        $5,820
   25  United States  Boot            New York       18               .      $495,479        $3,983
   26  United States  Men's Casual    New York       20        $456,985      $890,096       $13,453
   27  United States  Men's Dress     New York       18        $191,755      $658,134        $5,399
   28  United States  Sandal          New York        1            $554        $5,060           $23
   29  United States  Slipper         New York       24        $252,758    $1,059,333        $8,503
   30  United States  Sport Shoe      New York       23         $22,190      $141,140          $847
   31  United States  Women's Casual  New York       10        $178,842      $438,035        $4,236
   32  United States  Women's Dress   New York       17        $288,972    $1,129,697       $10,257
   33  United States  Boot            Seattle        15               .      $226,678        $3,039
   34  United States  Men's Casual    Seattle         6         $65,842      $112,538        $3,395
   35  United States  Men's Dress     Seattle        10        $132,397      $274,310        $3,153
   36  United States  Sandal          Seattle        10          $6,596       $18,768          $181
   37  United States  Slipper         Seattle        10        $155,255      $553,502        $5,277
   38  United States  Sport Shoe      Seattle        29         $30,408      $108,266        $1,021
   39  United States  Women's Casual  Seattle         2         $17,252       $28,513          $790
   40  United States  Women's Dress   Seattle        21        $133,405      $392,307        $4,037

This sample shows you how to modify a data item expression in an information map so that missing numeric values are displayed as zeros in query results.

Type:

Sample

Date Modified:	2009-06-05 11:22:38
Date Created:	2008-09-16 09:47:52

Operating System and Release Information

Product Family	Product	Host	Product Release		SAS Release
			Starting	Ending	Starting	Ending
SAS System	SAS Information Map Studio	Windows Vista	3.1		9.1 TS1M3 SP4
		Microsoft Windows XP Professional	3.1		9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Standard Edition	3.1		9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Enterprise Edition	3.1		9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Datacenter Edition	3.1		9.1 TS1M3 SP4
		Microsoft Windows NT Workstation	3.1		9.1 TS1M3 SP4
		Microsoft Windows 2000 Professional	3.1		9.1 TS1M3 SP4
		Microsoft Windows 2000 Server	3.1		9.1 TS1M3 SP4
		Microsoft Windows 2000 Datacenter Server	3.1		9.1 TS1M3 SP4
		Microsoft Windows 2000 Advanced Server	3.1		9.1 TS1M3 SP4
		Microsoft® Windows® for x64	3.1		9.1 TS1M3 SP4