4

I have a dataset with a column of phone numbers. I want to filter this dataset using PROC SQL WHERE the length of the numbers is at least 7 digits.

In normal SQL I can just apply a length function around a number and it works, however in SAS it won't let me apply it to a numerical column.

My first instinct is to convert the column to a character and then find the length of that but I can only seem to state a size when I use the put function. However I dont even know the biggest size of my numbers as I can't calculate length!

How do i find the length of a numerical value in SAS using PROC SQL?

abatishchev
  • 92,232
  • 78
  • 284
  • 421
shecode
  • 1,530
  • 4
  • 28
  • 41
  • How are you dealing with phone numbers that start with a zero? A format could still display the leading zeros, however the underlying numeric value will not and therefore your lengths could be wrong – Longfish Mar 05 '15 at 09:47
  • @Keith No phone number (in the US, at least) can start with zero, unless it is an international number and includes the `011` exit code (in which case it would be well over 7 digits). – Joe Mar 05 '15 at 15:07
  • @Joe Ah, ok. Better than here in the UK where they all start with a zero (which is why they're mainly stored as character in databases). I was thinking along the lines of using the `VVALUE` function in conjunction with `LENGTH` – Longfish Mar 05 '15 at 16:11
  • Unfortunately, `vvalue` is not available in PROC SQL. – Joe Mar 05 '15 at 16:12

3 Answers3

6

Since you have not posted the sample dataset , so I have created one for myself

Creating the sample dataset. Taking phonenum as numeric same as in your case.

data test;
infile datalines;
input phonenum : 8.;
datalines;
123
1234
12345
123456
1234567
12345678
123456789
12345678910
;
run;

You are right in the approach, if you want to count the number of digits, it has to be converted to char, doing the following steps below:

  1. Converting the numeric phonenum to char . Although it is obvious that the number of digits would not be greater than 32, still if you would like you can increase the count.
  2. Using the compress function to strip off the blank characters
  3. Using the length function to count the number of digits
  4. In proc sql\SAS you can not use the newly created variable in the where statement just like that, but proc sql allows you to do so using the calculated keyword before such type of variables.
proc sql;
select length(compress(put(phonenum,32.))) as phonelen from test where calculated phonelen > 6;
quit;

Additionally, you could achieve the same using datasteps(SAS) as well like below:

data _null_;
set test;
phonelen=length(compress(input(phonenum,$32.)));
if phonelen > 6;
put phonelen=;
run;
NEOmen
  • 1,908
  • 1
  • 12
  • 22
1

In SAS, length() takes a character string as argument(only).

You would have to convert the numeric variable to character:

proc sql;
    select length(put(x,32. -l)) from test;
quit;

to use that function. The -l left aligns the result (so the extra spaces are ignored). You can arbitrarily choose 32 (as that's much longer than it should be) or any other value at least 10 or so (determine this from your likely numeric values- can this have a country code, etc.).

Of course, you could always just say

numvar ge 1000000

which would do the same, no?

And of course, a phone number should never be stored in a numeric field. 7 digit number takes 7 bytes as character, 8 as number, and while it contains 7 digits it's really not a numeric concept.

Joe
  • 58,871
  • 5
  • 42
  • 60
  • I found when i did length(put(numvar,32.)), then looked at the distribution of lengths of the numvar, everything returned as 32. I wanted to see how many digits the numbers have but this doesn't do it for me – shecode Mar 04 '15 at 21:20
  • Ah - it right-aligns the number, I forgot about that. – Joe Mar 04 '15 at 21:23
-2

I would suggest using magic: 

log10(numericphonenumber)>6
uzbad
  • 106
  • 1
  • 4