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

[kariwarez]

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

[ChrisBrooks · Accepted solution]

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;

[kariwarez]

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

[PGStats]

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;

[kariwarez]

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

[PGStats]

I learned something too, thanks @ChrisBrooks!