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
- Open the Expression Editor for the data item. In this sample, the data item is Sales.
- 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
|
- Click OK to save the expression.
- 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.
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
