BookmarkSubscribeRSS Feed
damanaulakh88
Obsidian | Level 7

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

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

damanaulakh88
Obsidian | Level 7

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

LinusH
Tourmaline | Level 20

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 544 views
  • 0 likes
  • 4 in conversation