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?
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:
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.compress
function to strip
off the blank characterslength
function to count the number of digitsproc 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;