I am trying to select numeric data that is greater than 5 bytes. I have entered the below but get an error of ERROR: Function LENGTH requires a character expression as argument 1. Not sure what I am doing wrong. Any help would be greatly appreciated.
29 PROC SQL;
30 CREATE TABLE WORK.FILTER_FOR_FDRMASTER_DAILY__0000 AS
31 SELECT t1.Account_id_nr,
32 t1.Predecessor_CC_SK,
33 t1.Successor_CC_SK,
34 t1.Related_CC_SK,
35 t1.Original_CC_SK,
36 t1.Predecessor_Argt_SK,
37 t1.Successor_Argt_SK,
38 t1.Predecessor_OK,
39 t1.Successor_OK,
40 t1.CHD_SSR_IN,
41 t1.CHD_SYSTEM_BANK,
42 t1.USAANR
43 FROM WORK.FILTER_FOR_FDRMASTER_DAILY_2 t1
44 WHERE t1.Successor_CC_SK NOT = . AND t1.CHD_SSR_IN = 'Y' AND t1.CHD_SYSTEM_BANK NOT BETWEEN '867700000100' AND
45 '867700009500' AND LENGTH(t1.Successor_CC_SK) > '5'
46 ORDER BY t1.Successor_CC_SK;
Advertisement
Answer
Since you are dealing with both credit card numbers and 5-digit numbers, let’s assume that all of them could be stored in one of three formats:
Num: 1234567890123456
Char: 1234-5678-9012-3456
Char: 1234 5678 9012 3456
Let’s start with the first case, which assumes that the variable is numeric. You want to exclude all 5-digit values. You can use this trick by Rick Wicklin to count the number of digits in an integer:
proc sql noprint;
create table want as
select number
from have
where ceil(log10(number+1)) > 5
;
quit;
For the second and third cases, they’ll be stored as characters. You can count the number of numeric characters in a string using countc()
with the D
option.
proc sql noprint;
create table want as
select compress(number, ' -')
from have
where countc(number,'','D') > 5
;
quit;