DATA Step, Macro, Functions and more

A little help with a Where clause?

Accepted Solution Solved
Reply
Regular Contributor
Posts: 212
Accepted Solution

A little help with a Where clause?

Please see the following code:

 

proc report data=nicholas.unisummary__7i nowd;
column
i_X _VAR_ _NOBS_ _MEAN_ _MIN_ _P1_ _P5_ _P10_
_Q1_ _MEDIAN_ _Q3_ _P90_ _P95_ _P99_ _MAX_ ;

where _VAR_ = "Ratio_1a" ;
where also i_X like "%21503%" ;
where also i_X like "%21305%" ;

run;

The third Where clause is causing a problem.

 

Using the first two Where clauses brings up the expected total number of rows (6).

 

Adding the third results in 0 hits.

 

Should be twice the number of hits as using the first two (12).

 

Suggestions greatly appreciated.

 

Nicholas Kormanik

 

 


Accepted Solutions
Solution
‎09-02-2016 07:12 PM
Super User
Posts: 10,466

Re: A little help with a Where clause?

That looks like an AND problem similar to:  if A like "%3%" AND A like "%Q%"

You really are not going to get both bits true.

Did you try anything like:

 

where also i_X like "%21503%"  OR  i_X like "%21305%" ;

 

View solution in original post


All Replies
Solution
‎09-02-2016 07:12 PM
Super User
Posts: 10,466

Re: A little help with a Where clause?

That looks like an AND problem similar to:  if A like "%3%" AND A like "%Q%"

You really are not going to get both bits true.

Did you try anything like:

 

where also i_X like "%21503%"  OR  i_X like "%21305%" ;

 

Regular Contributor
Posts: 212

Re: A little help with a Where clause?

Thanks ballardw.  I thought it was an AND problem.

 

This as well as this, as well as this, too.  Not OR.

 

Plus I wasn't sure how exactly to set it up.

 

After your change, is it now written in the most efficient way?

 

 

Super User
Posts: 10,466

Re: A little help with a Where clause?

"Effecient" is sometimes subjective depending on your needs. With computers you often are trading of CPU cycles, memory, input/output use and code maintenance.

If you are comparing with hundreds of values then likely a join may be a better approach. If you are using Like where the value you are looking for either starts with or is equal to the values you've shown then possibly other comparisons may be better.

For instance if the real values are equal then In would likely be a better (code maitenance, easy of reading) approach:  Value In ("12345" "34567"). Of if you know that the bit you are looking for always occurs in postions 3 to 7 in a string it might be faster (CPU cyles) to substring and then use the In operator.

 

You know your data. If the value(s) do not appear with any pattern then perhaps Like is the best approach.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 293 views
  • 2 likes
  • 2 in conversation