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
- /
- BI
- /
- Enterprise Guide
- /
- Where Character Length = X

Topic Options

- 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
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-12-2018 05:26 PM

Quick question all,

I need Proc Sql syntax for a where statement bring back only records with a string length of 10.

I tried ths and it didn't work:

```
where a.date_created >=02/20/2018
and length(account_number = 10)
```

Thanks for any suggestions!

Accepted Solutions

Solution

03-12-2018
05:36 PM

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

Posted in reply to Dogo23

03-12-2018 05:33 PM

All Replies

Solution

03-12-2018
05:36 PM

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

Posted in reply to Dogo23

03-12-2018 05:33 PM

```
where a.date_created >=02/20/2018
and length(account_number) = 10
```

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

Posted in reply to novinosrin

03-12-2018 05:36 PM

Strange! Your's matches mine so maybe there was a minor difference between what I posted and what I actually ran. This works. Thanks!

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

Posted in reply to Dogo23

03-12-2018 05:37 PM

In addition to the suggestion from @novinosrin that moves one of the closing parentheses:

- A WHERE statement requires a semicolon, and
- 02/20/2018 is the wrong way to refer to a date. This actually means 2 divided by 20, divided by 2018. Instead of 02/20/2018:

'20Feb2018'd

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

Posted in reply to Dogo23

03-12-2018 05:44 PM

hi @Dogo23 to help you clarify a bit more, see the below demo

```
data h;
input var $10.;
datalines;
9999999999
99999
9999999999
8888888888
777777
888
;
data w;
set h;
length=length(var);
length2=length(var)=10;/*your were trying to compute the length of binary result which will always result in 1*/
if length(var=10) then flag=1;/*your were trying to compute the length of binary result*/
run;
```

Your check was a binary check nested before the length function computes it's work. The nested binary will result in 1 or 0 for values that is 10 and "Not aka 0" for otherwise. The commented section should help i think. Thank you.