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

shecode picture shecode · Mar 4, 2015 · Viewed 24k times · Source

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?

Answer

NEOmen picture NEOmen · Mar 5, 2015

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;