Put function in where statement takes longer time.

Reply
Frequent Contributor
Posts: 81

Put function in where statement takes longer time.

wherefunctionsHi,

I am trying to apply a user defined format through a put funtion in the where statement while setting the dataset and it it is taking 1 hour, and if i remove the put function, it takes 3 minutes.

What can be the issue , and how can we overcome it ?

Thanks in advance

Esteemed Advisor
Esteemed Advisor
Posts: 7,192

Re: Put function in where statement takes longer time.

Well, posting the code would be useful.  I wouldn't recommend putting functions like that in a where clause as its called for each row then totaling up time.  What are you trying to where, why not use the underlying data, e.g if date then '01JAN2014'd, if number then =1, etc.

Esteemed Advisor
Posts: 6,640

Re: Put function in where statement takes longer time.

Post the proc format code and the data step.

Most probably the user defined format is complex enough to eat CPU cycles.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 81

Re: Put function in where statement takes longer time.

I am using the cntlin option to create a format for a variable and tehn applying to a bigger dataset.

Esteemed Advisor
Posts: 6,640

Re: Put function in where statement takes longer time.

Aside from the possibilty of overriding an index, POST THE CODE!

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Esteemed Advisor
Posts: 5,194

Re: Put function in where statement takes longer time.

With that kind of difference I suspect that your column is indexed.

Putting a function in where clause effectively removes the possibility to use index (with some exceptions, like substr from pos 1).

"Note: Conditions are not optimized with an index for arithmetic operators, a variable-to-variable condition, the sounds-like operator, and any function other than the TRIM and SUBSTR function as listed above."

Understanding SAS Indexes :: SAS(R) 9.4 Language Reference: Concepts, Fourth Edition

If your value is a constant, create is a macro variable prior to the extract step.

If it't in a transaction/look-up table, have the put function in an earlier step.

Data never sleeps
Ask a Question
Discussion stats
  • 5 replies
  • 219 views
  • 0 likes
  • 4 in conversation