How to find the length of a numerical variable using PROC SQL

Question

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?

Solution 1

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;

Solution 2

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.