topic Re: Count how many times a surname appears in an email address in SAS Programming

Count how many times a surname appears in an email address

ChrisJones92 — Thu, 25 Mar 2021 18:32:28 GMT

Hi everyone, thanks in advance for the help.

Essentially, I have an email address variable and a lastname variable in a SAS dataset. What I'm trying to do is count how many times the lastname appears in the email address for that row. As an example:

Lastname: Smith

Email: Smithtestsmith@test.test

Should return a count of two.

I've tried fiddling with a rudimentary array, but I can only get it to count once. I'm sure this is possible, but I can't think how for the life of me. Both these variables are upper case, so case matching won't be an issue.

data Sales_Te;

set Sales;

leng=length(lastname);

do i=1 to leng;

if substr(email,i,leng) = lastname then name=1;

drop leng;

end;

run;

Re: Count how many times a surname appears in an email address

ChrisJones92 — Thu, 25 Mar 2021 18:37:05 GMT

Sorry, leng in my array should be 'leng=length(email), but it still won't do what I need it to. Thanks

Re: Count how many times a surname appears in an email address

PeterClemmensen — Thu, 25 Mar 2021 18:41:49 GMT

See if this approach works for you

data _null_;
s = "Smith";
m = "Smithtestsmith@test.test";

do i = 1 to length(m) - length(s);
   if lowcase(substr(m, i, length(s))) = lowcase(s) then count + 1;
end;

put count =;
 
run;

Re: Count how many times a surname appears in an email address

PeterClemmensen — Thu, 25 Mar 2021 18:49:55 GMT

Also:

data _null_;
   s = "Smith";
   m = "Smithtestsmith@test.test";
   count = count(m, s, 'i');
   put count =;
run;

Re: Count how many times a surname appears in an email address

ChrisJones92 — Thu, 25 Mar 2021 19:35:41 GMT

Hi Peter,

This is almost perfect for what I need, so thank you for that!

Is there a way to make the count a row-wise count, rather than a cumulative count? So, if I were to have say 100 rows, can I make the count the number of matches in each row?

Many thanks,
Chris

Re: Count how many times a surname appears in an email address

ballardw — Thu, 25 Mar 2021 19:52:47 GMT

data _null_; s = "Smith"; m = "Smithtestsmith@test.test"; do i = 1 to length(m) - length(s); if lowcase(substr(m, i, length(s))) = lowcase(s) then count =sum(count, 1); end; put count =; run;

The code "then count+1" creates an implied Retain statement, which keeps the values across data step iteration boundaries. Using count = sum(count,1); instead adds 1 each time the statement is true but resets for each statement. If the condition is never true for a record the result will be missing. The Sum function only adds, or sums, non-missing values so does not have any oddities that + would have when count is undefined or missing such as the first time the condition is true.

Re: Count how many times a surname appears in an email address

ChrisJones92 — Thu, 25 Mar 2021 19:58:30 GMT

Hi Ballardw,

This is perfect and does exactly what I need it to! Huge thanks to both you and Peter for your help.

Chris

Re: Count how many times a surname appears in an email address

PGStats — Thu, 25 Mar 2021 20:11:25 GMT

You could use regular expressions this way:

data test;
name = "Smith";
length dum $200;
do email = "Smithtestsmith@test.test", "test@test.test";
    id = prxParse(cats("s/",name,"//i"));
    call prxChange(id, -1, email, dum, len, trunc, nb);
    call prxFree(id);
    output;
    end;
drop dum id len trunc;
run;