DATA Step, Macro, Functions and more

coalesce or case-when efficiency

Reply
Super Contributor
Posts: 321

coalesce or case-when efficiency

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
SAS Super FREQ
Posts: 8,745

Re: coalesce or case-when efficiency

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...

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
Super Contributor
Posts: 321

Re: coalesce or case-when efficiency

Thank you very much for the input Cynthia.

Milton
Ask a Question
Discussion stats
  • 2 replies
  • 204 views
  • 0 likes
  • 2 in conversation