SAS Replace dots (nulls) with zero


I have imported a file from Excel into SAS Enterprise Guide 4.3 and have performed some calculations on the data.  I need to replace the dots or nulls with zero.  I'm hoping to be able to do this in the "interactive" bit as I'm fairly new to SAS and haven't got as far as coding yet.

Any idea how I can do this?  I'm awarding 500 points for a quick, working answer.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aloysius LowCommented:
If you wish to permanently replace null worth zero, you can use the coalesce function
coalesce(field, 0);

otherwise, if you wish to directly perform operations like add or subtract, use the sum function, which will treat null as zero.. directly using the mathematical operators will result in null
ScuzzyJoAuthor Commented:
Hi Iowaloysius

I've used the sum function, which is working fine in this case as the two numbers I want to add have one as a positive and one as a negative.  If they were both positive, how would I do that?  Can you nest the field1  - field2 inside a coalesce function or do you have to calculate the two columns separately first - once to replace the nulls with zero and once to turn one of them to a negative?  Obviously, doing it in one go would be more efficient, but I'm not sure whether or not I can do this?

Aloysius LowCommented:
Hi Sarah,

The SUM function performs exactly like the + operator, with an added benefit of handling NULL values, which will cause the + operator to return NULL. Therefore, the parity of the number doesn't matter i.e. 2 positive numbers will result in a larger positive number, 2 negative numbers will result in a larger negative number, 1 positive and 1 negative number will result in a number in-between the 2, while a NULL and another number will result in the other number itself.

If you are already using the SUM function, then there is no need to use the coalesce function, unless you wish to set NULL values to a number that is not zero. If that's the case, then you will need to coalesce the 2 numbers separately, as coalesce will return you the first available non-zero number.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.