BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ChrisJones92
Fluorite | Level 6

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User
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.

View solution in original post

7 REPLIES 7
ChrisJones92
Fluorite | Level 6
Sorry, leng in my array should be 'leng=length(email), but it still won't do what I need it to. Thanks
PeterClemmensen
Tourmaline | Level 20

Also:

 

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

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;
ChrisJones92
Fluorite | Level 6
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
ballardw
Super User
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.

ChrisJones92
Fluorite | Level 6
Hi Ballardw,

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

Chris
PGStats
Opal | Level 21

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;

PGStats_0-1616702989574.png

 

PG

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 1600 views
  • 3 likes
  • 4 in conversation