Hello all,   I'm looking for some insight/direction on the following:   I have 5 columns of data, A-E. I want to compare column E with columns A-D and count where they are not equal for all rows. However, columns A-D may have the same record in one or more columns, or might contain a missing record that I don't want to count.    I've started with a string of proc sql statements as follows:   proc sql; create table one as select count (*) from combined where A = E and A not in (' '); create table two as select count(*) from (select * from combined where A <> B and B not in (' ')) where B = E; create table three as select count(*) from (select * from combined where A <> C and C not in (' ')) where C = E; create table four as select count(*) from (select * from combined where A <> D and D not in (' ')) where D = E; quit; However, when I QA some of these results I see that I'm counting records that I shouldnt across some of the latter tables. I've played around with adding some additional exclusion criteria but I can't seem to get it right. I think their is an easier way to compare the data in these columns.    Appreciate any thoughts.    Happy New Year! ... View more

Hello,   I recently posted a question about phone number validation, etc, and was referred to this article: https://heuristically.wordpress.com/2012/10/30/pho ne-number-validation-in-sas/   It's a great article and the program works fine, however, when i look through my results I'm not getting the flags I would expect based on how that portion of the program is written.   /* A=area code not in service */ if substr(&sm_phone_number, 1, 3) not in (&mv_npa) then &sm_exception = 'A'; /* R=repeating number like 5555555555 is a probable fake */ if prxmatch('/^([0-9])(\1{9})$/', strip(&sm_phone_number)) eq 1 then &sm_exception = 'R'; /* I=Skype and Google GMail phone numbers do not allow inbound calls */ if &sm_phone_number in ('2025808200', '7607058888') then &sm_exception = 'I'; /* D=directory assistance <https://en.wikipedia.org/wiki/555-1212> */ if prxmatch('/^[0-9]{3}5551212$/', trim(&sm_phone_number)) eq 1 then &sm_exception = 'D'; /* F=numbers specifically reserved for fictional use are "555-0100" through "555-0199" */ if prxmatch('/^[2-9][0-8][0-9]55501[0-9]{2}$/', strip(&sm_phone_number)) eq 1 then &sm_exception = 'F'; /* fake */ /* 1=the last two digits of NXX cannot both be 1, to avoid confusion with the N11 * codes (http://en.wikipedia.org/wiki/North_American_Numbering_Plan) * Only non-geographic area codes, such as toll-free 800/888/877/866/855 numbers * and 900 numbers may use N11 as the telephone exchange prefix, since * the area code must always be dialed for these numbers. * <https://en.wikipedia.org/wiki/N11_code> */ if (prxmatch('/^(800|888|877|866|855|900)/', strip(&sm_phone_number)) ne 1) and (prxmatch('/^[2-9][0-8][0-9][2-9]11[0-9]{4}\b/', strip(&sm_phone_number)) eq 1) then &sm_exception = '1'; /* S=basic NANP syntax */ if prxmatch('/^\(?[2-9][0-8][0-9]\)? ?[2-9][0-9]{2}-?[0-9]{4}\b/', strip(&sm_phone_number)) ne 1 then &sm_exception = 'S';   For example, all phone numbers in my dataset with sequential numbers like '000000000' or '9999999998', etc, are being flagged as "S" instead of "R" and I can't figure out why. I've been reading all the literature online regarding PRXMatch but it's not clicking for me.    A thought just occured to me, could this be because my phone number fields are all text?   I appreciate any insight into this. Thanks! ... View more