Hello!
I am having trouble figuring out how to select a certain column whose string starts with the letter C and ends with 8-10 numbers only. I know how to use 'C%' but am unable on how to figure out the string length in range part. Below I have it pulling anything from address that start with C. I just need to figure out how to implement the range of 8-10 numbers that follow that C into the sample code below.
Any ideas?
SELECT c.NEIGHBORS,
c.COUNTRY,
c.ADDRESS,
FROM DATA.SAMPLE a
LEFT JOIN DATA.SAMPLE_DIM b ON (a.CAR = b.CAR)
LEFT JOIN DATA.EXTRA c ON (a.COLOR = c.COLOR)
WHERE a.CAR LIKE 'Toyota' AND c.INTERNET = 'Charter' AND c.ADDRESS LIKE 'C%';
QUIT;
The COUNTC function can count the number of specific characters:
The function searches in the first parameter, the string to search, for the characters in the second position. The 'd' add all the digits so is just quicker than typing all the 0 through 9.
Caveat: this will count the digits even if there are spaces or other letters in the middle. Since you haven't provided an actual examples of the string to search I am starting with a simple case. If you have other stuff you need to provide examples and which match your rule(s).
data example; input str :$15.; nums = countc(str,'1','d'); datalines; C1 C22 C345 C444 C4567 C12345678 C1234566788
C1111111111 ;
@RedUser77 wrote:
Hello!
I am having trouble figuring out how to select a certain column whose string starts with the letter C and ends with 8-10 numbers only. I know how to use 'C%' but am unable on how to figure out the string length in range part. Below I have it pulling anything from address that start with C. I just need to figure out how to implement the range of 8-10 numbers that follow that C into the sample code below.
Any ideas?
SELECT c.NEIGHBORS,
c.COUNTRY,
c.ADDRESS,
FROM DATA.SAMPLE a
LEFT JOIN DATA.SAMPLE_DIM b ON (a.CAR = b.CAR)
LEFT JOIN DATA.EXTRA c ON (a.COLOR = c.COLOR)
WHERE a.CAR LIKE 'Toyota' AND c.INTERNET = 'Charter' AND c.ADDRESS LIKE 'C%';
QUIT;
Untested:
and prxmatch('/C\d{8,10}/', c.address)
Thank you. Now, this does work if I were to use this statement three times on two other columns following the same format? I have two other columns I want to pull where data starts with C and has anywhere from 8-10 digits following. Would I just put the whole statement in () with OR inside?
Like: AND (PRXMATCH('/C\d{8,10}/', c.ADDRESS) OR PRXMATCH('/C\d{8,10}/', c.ADDRESS_1) OR PRXMATCH('/C\d{8,10}/', c.ADDRESS_2))
Yes.
data example; input str $15.; if prxmatch('/^C\d{8,10}$/i',strip(str)) then flag=1; datalines; C1 C22 C345 C444 C4567 C12345678 C1234566788 C1111111111 ;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.