SQL 如何使用PROC SQL查找数值变量的长度
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28864703/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to find the length of a numerical variable using PROC SQL
提问by shecode
I have a dataset with a column of phone numbers. I want to filter this dataset using PROC SQL WHEREthe length of the numbers is at least7 digits.
我有一个包含电话号码列的数据集。我想使用 PROC SQL 过滤此数据集WHERE，数字的长度至少为7 位。
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.
在普通的 SQL 中，我可以只在一个数字周围应用一个长度函数并且它可以工作，但是在 SAS 中它不会让我将它应用于数字列。
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 putfunction.
However I dont even know the biggest size of my numbers as I can't calculate length!
我的第一直觉是将列转换为字符，然后找到它的长度，但我在使用该put函数时似乎只能说明大小。但是我什至不知道我的数字的最大大小，因为我无法计算长度！
How do i find the length of a numerical value in SAS using PROC SQL?
如何使用 PROC SQL 在 SAS 中找到数值的长度？
回答by NEOmen
Since you have not posted the sample dataset , so I have created one for myself
由于您还没有发布示例数据集，所以我为自己创建了一个
Creating the sample dataset. Taking phonenumas numericsame as in your case.
创建示例数据集。以phonenum如numeric在你的情况一样。
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:
您是正确的方法，如果您想计算位数，则必须将其转换为char，执行以下步骤：
- Converting the
numericphonenum tochar. 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. - Using the
compressfunction tostripoff the blank characters - Using the
lengthfunction to count the number of digits - In
proc sql\SASyou can not use the newly created variable in thewherestatement just like that, butproc sqlallows you to do so using thecalculatedkeyword before such type of variables.
- 将
numeric电话号码转换为char. 虽然很明显数字的位数不会大于 32，但如果您愿意，仍然可以增加计数。 - 使用
compress函数strip去除空白字符 - 使用
length函数计算位数 - 在
proc sql\SAS您不能where像那样在语句中使用新创建的变量，但proc sql允许您calculated在此类变量之前使用关键字来这样做。
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:
此外，您可以使用 datasteps(SAS) 实现相同的效果，如下所示：
data _null_;
set test;
phonelen=length(compress(input(phonenum,.)));
if phonelen > 6;
put phonelen=;
run;
回答by Joe
In SAS, length()takes a character string as argument(only).
在 SAS 中，length()将字符串作为参数（仅）。
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 -lleft 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.).
使用该功能。在-l左对齐的结果（所以多余的空格被忽略）。您可以任意选择 32（因为这比它应该的要长得多）或任何其他值至少为 10 左右（从您可能的数值中确定 - 这是否有国家/地区代码等）。
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.
当然，电话号码永远不应该存储在数字字段中。7 位数字以 7 个字节为字符，8 个为数字，虽然它包含 7 位数字，但它实际上不是一个数字概念。
回答by uzbad
I would suggest using magic:
我建议使用魔法：
log10(numericphonenumber)>6
