topic Re: A little help with a Where clause? in SAS Programming

A little help with a Where clause?

NKormanik — Fri, 02 Sep 2016 21:29:07 GMT

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

Re: A little help with a Where clause?

ballardw — Fri, 02 Sep 2016 21:41:28 GMT

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%" ;

Re: A little help with a Where clause?

NKormanik — Fri, 02 Sep 2016 23:18:10 GMT

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?

Re: A little help with a Where clause?

ballardw — Tue, 06 Sep 2016 16:22:14 GMT

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