turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- A little help with a Where clause?

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-02-2016 05:29 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-02-2016 05:41 PM

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

All Replies

Solution

09-02-2016
07:12 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-02-2016 05:41 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-02-2016 07:18 PM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-06-2016 12:22 PM

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