topic coalesce or case-when efficiency in SAS Programming

coalesce or case-when efficiency

milts — Mon, 09 Aug 2010 01:39:08 GMT

hi experts,

i'd like to check a variable if it is null or missing. if so, i'll assign the value 0 to another column, else the other column will just take it's value.

which between these two is more efficient?

case when var1 not is missing then var1 else 0 end as var2

OR

coalesce(var1,0) as var2

thanks,
milton

Re: coalesce or case-when efficiency

Cynthia_sas — Mon, 09 Aug 2010 03:35:01 GMT

Hi:
This might be one of those instances where "your mileage may vary" depending on the rest of your query and the size of the file(s) being accessed/created. Running some benchmarks might be in order.

A review of the doc might be in order too:
http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#/documentation/cdl/en/proc/61895/HTML/default/a002206368.htm

I have to think there was -some- reason for this note in the doc:

Note: If your query contains a large number of COALESCE function calls, it might be more efficient to use a natural join instead. See Natural Joins.

cynthia

Re: coalesce or case-when efficiency

milts — Tue, 10 Aug 2010 00:50:50 GMT

Thank you very much for the input Cynthia.

Milton