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

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

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisBrooks
Ammonite | Level 13

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

5 REPLIES 5
ChrisBrooks
Ammonite | Level 13

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
Calcite | Level 5

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
Opal | Level 21

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
kariwarez
Calcite | Level 5

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
Opal | Level 21

I learned something too, thanks @ChrisBrooks!

PG

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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