topic Convert Null to Zero in SAS Enterprise Guide

Convert Null to Zero

deleted_user — Mon, 20 Aug 2007 23:20:50 GMT

I am creating a computed column in a query task of SAS EG 4.1. For the computed column I am subtracting field b from field a, for example (a-b). a has a value of 643 and column b, for this particular row, has a value of null. My result ends up being null instead of 643. How do I get the query task to treat the null as a zero so that my computed column has a resulting value of 643?

As a side note, in some cases column b has a value other than null, so it is only for the calculations where b is equal to null that I need b to be recognized as a 0.

Please help if you can. Thank you!
Jackie

Re: Convert Null to Zero

deleted_user — Tue, 21 Aug 2007 07:48:55 GMT

hopefully you can use data-step functions in your compute

result = sum( value1, valueX, anotherValue ) ;

The result holds the sum of non-missing variables .

proper doc for the base SAS sum function is at http://support.sas.com/onlinedoc/913/getDoc/en/lrdict.hlp/a000245953.htm

I'm not sure if there is a corresponding place for anything E.G.-specific for computed columns in a query

Good Luck

PeterC

Re: Convert Null to Zero

deleted_user — Tue, 21 Aug 2007 15:01:51 GMT

Thank you Peter this worked very well. This is what I did...

From the computed column box I selcted the "functions" tab and picked "sum". Then I clicked on the "data" tab and built the computation like this...

SUM(TableA.FieldA, -TableB.FieldB). I added in the negative sign before the second variable (TableB.FieldB) in order for the sum to work properly for subtracting FieldB.

This is just what I needed. Thanks again!!!
Jackie