turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- BI
- /
- Enterprise Guide
- /
- Convert Null to Zero

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-20-2007 07:20 PM

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

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to deleted_user

08-21-2007 03:48 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to deleted_user

08-21-2007 11:01 AM

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

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