Hello, While testing a macro to return email addresses from a metadata dataset, I get (seemingly) inconsistent results from the FINDW and INDEXW functions. When running the code given below, I am really curious why the datasets A1 and A2 are returning different results, and why the datasets B1 and B2 are returning different results. It seems like the order in which I check things influences the result. My feeling is that A1 and A2 should be same, and that B1 and B2 should be same. Can anybody give me a hint why they are not? I get: NOTE: Table WORK.TEST_A1 created, with 1 rows and 2 columns. NOTE: Table WORK.TEST_A2 created, with 0 rows and 2 columns. NOTE: Table WORK.TEST_B1 created, with 0 rows and 2 columns. NOTE: Table WORK.TEST_B2 created, with 1 rows and 2 columns. I am running in SAS EG 7.1 on UNIX, and in SAS 9.4 on WINDOWS. data T_USER_EMAIL;
length userid $6 email $100;
userid='ABC090'; email='john.smith@xyz.com'; output;
userid='DEF090'; email='joanna.smythe@xyz.com'; output;
userid='HIJ090'; email=' '; output;
run;
* test with Name;
proc sql noprint;
create table test_A1 as
select *
from T_USER_EMAIL
where index(upcase(email),'@XYZ.COM')
and ( (FINDW(" JOHN.SMITH xxx",strip(upcase(scan(email,1,'@'))),' ')) or
(FINDW(" JOHN.SMITH xxx",strip(upcase(userid)))) ) ;
quit;
proc sql noprint;
create table test_A2 as
select *
from T_USER_EMAIL
where index(upcase(email),'@XYZ.COM')
and ( (FINDW(" JOHN.SMITH xxx",strip(upcase(userid)))) or
(FINDW(" JOHN.SMITH xxx",strip(upcase(scan(email,1,'@'))),' ')) ) ;
quit;
* test with UserId;
proc sql noprint;
create table test_B1 as
select *
from T_USER_EMAIL
where index(upcase(email),'@XYZ.COM')
and ( (FINDW(" DEF090 xxx",strip(upcase(scan(email,1,'@'))),' ')) or
(FINDW(" DEF090 xxx",strip(upcase(userid)))) ) ;
quit;
proc sql noprint;
create table test_B2 as
select *
from T_USER_EMAIL
where index(upcase(email),'@XYZ.COM')
and ( (FINDW(" DEF090 xxx",strip(upcase(userid)))) or
(FINDW(" DEF090 xxx",strip(upcase(scan(email,1,'@'))),' ')) ) ;
quit; (Just fyi, I want Users to be able to provide UserIds and/or Names to the macro ... that is why the code generated by the macro is checking in both the userid and email columns)
... View more
