DATA Step, Macro, Functions and more

Count the number of a specific word in a character field in PROC SQL

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Count the number of a specific word in a character field in PROC SQL

[ Edited ]

In a dataset with more than a million rows, I have a character field called SERVICE that has a length of 1000 bytes.

 

The value of SERVICE looks like:

ABC123,XYZ456,BRK000,ENG789,ABC963,ABC778

 

Basically, 3 characters followed by 3 digits combined. That is the pattern.

There are multiple values like that separated by comma. I've provided a small example above but the total length can go up to 1000 at the most.

 

I want to create a new variable called COUNT_ABC which contains a value that represents the number of occurences of the word ABC in the field SERVICE.

For the above example, the value of COUNT_ABC should be 3.

 

The challenge I'm facing here is to accomplish this in PROC SQL because this is an addition to an existing modeling code that is creating about 800 variables using lots of calculations and aggregations.

 

I cannot use DATA step.

 

If the length of the field is less than 200 bytes, I would use the following logic:

 

PROC SQL;
CREATE TABLE FINAL AS
SELECT (LENGTH(SERVICE) - LENGTH(TRANSTRN(SERVICE,"ABC",TRIMN(" "))))/3 AS COUNT_ABC
FROM INPUT;
QUIT;

 

 

Since TRANSTRN is a SAS character function that truncates the output to 200 bytes, this method won't work in my case where the length can go up to 1000 bytes.

 

 

I'm anxious to solve this in PROC SQL and without the use of user/custom defined functions.

 

 

Maybe there is way to solve this using a PRX function? I'm not an expert in this though.

 

The following logic removes all ABCs.

SELECT PRXCHANGE('s/(?:ABC)/$1/i',-1,SERVICE) AS ONLY_ABC

 

So the resulting text is like:

123,XYZ456,BRK000,ENG789,963,778

 

If I could find a way to do the opposite(that is to keep only the ABCs and strip off everything else including numbers and commas)

Like:

ABCABCABC

 

Then I can use the length function to get the count because the maximum length in that case will only be like around 60.

 

Thanks


Accepted Solutions
Solution
3 weeks ago
Valued Guide
Posts: 593

Re: Count the number of a specific word in a character field in PROC SQL

Posted in reply to kariwarez

Can't you simply use the COUNT function like so?

 

data have;
	length service $1000;
	infile datalines;
	input service;
	datalines;
ABC123,XYZ456,BRK000,ENG789,ABC963,ABC778
;
run;

proc sql;
	create table want as
	select service, count(service,"ABC") as count_abc
	from have;
quit;

View solution in original post


All Replies
Solution
3 weeks ago
Valued Guide
Posts: 593

Re: Count the number of a specific word in a character field in PROC SQL

Posted in reply to kariwarez

Can't you simply use the COUNT function like so?

 

data have;
	length service $1000;
	infile datalines;
	input service;
	datalines;
ABC123,XYZ456,BRK000,ENG789,ABC963,ABC778
;
run;

proc sql;
	create table want as
	select service, count(service,"ABC") as count_abc
	from have;
quit;
New Contributor
Posts: 3

Re: Count the number of a specific word in a character field in PROC SQL

Posted in reply to ChrisBrooks

Well this is really embarrassing. I've been programming in SAS for 5 years and this will haunt me forever.

I was looking at all the wrong places for almost 10 hours. I wouldn't have even thought to look at this function. Completely overlooked it.

 

Thank you

Esteemed Advisor
Posts: 5,526

Re: Count the number of a specific word in a character field in PROC SQL

Posted in reply to kariwarez

If you reserve a large enough space for the function result, TRANTRN will not truncate:

 

data input;
call streaminit(99796);
length service $1000;
service = "XYZ123";
do i = 1 to 99;
    service = cats(service,",ABC",put(1000*rand("uniform"),z3.0));
    end;
drop i;
run;

PROC SQL;
CREATE TABLE FINAL(drop=s2) AS
SELECT 
    TRANSTRN(SERVICE,"ABC",TRIMN(" ")) as s2 length=1000,
    (LENGTH(SERVICE) - LENGTH(calculated s2))/3 AS COUNT_ABC
FROM INPUT;
select * from final;
QUIT;
PG
New Contributor
Posts: 3

Re: Count the number of a specific word in a character field in PROC SQL

[ Edited ]

So I cannot create an intermediate variable like S2 in your example.

I can't use DROP=S2 because I'm using the FEEDBACK option in PROC SQL and another program scans the log to recreate the entire query. And if you've used FEEDBACK option, you'd notice that the log doesn't print the "CREATE TABLE" part. It only starts printing from the SELECT clause.

My code has lots of loops and macros. The code has like 60 rows of lines but when expanded, it is creating around 800 variables.

I just cannot use any intermediate variable.

Sorry if I didn't make this clear in my question.

There are really a ton of ways to solve this but not a lot with my limitation.

 

But I got the solution from @ChrisBrooks.. To use the COUNT function.

I'm familiar with COUNTW, COUNTC functions.

I always thought COUNT only works the way it works in SQL and never realized it was also a SAS function.

 

Thanks

Esteemed Advisor
Posts: 5,526

Re: Count the number of a specific word in a character field in PROC SQL

Posted in reply to kariwarez

I learned something too, thanks @ChrisBrooks!

PG
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 113 views
  • 2 likes
  • 3 in conversation