topic Re: Finding a Specific letter in Specific position in text in SAS Programming

Finding a Specific letter in Specific position in text

ybz12003 — Wed, 29 May 2019 17:10:28 GMT

Hello:

I would like to find 'P' letter in the first position in my dataset 'ID' column. I would like to use Proc SQL, not data step. Is there a way to do that? Thank you.

For example, my ID column is list:

1. PCR00026

2. NCR00025

3. PCR00027

4. NCR00033

5. NCR00055

Therefore, I would like to find the ID #1 and ID#3.

proc sql;
	create table Want as
 	select ID 
 	from test
 	where ID....  
 	order by id;

Re: Finding a Specific letter in Specific position in text

GPatel — Wed, 29 May 2019 17:24:41 GMT

DATA A;
INPUT ID $;
CARDS;
PCR00026
NCR00025
PCR00027
NCR00033
NCR00055
;
RUN;
PROC SQL;
CREATE TABLE b AS
SELECT * FROM a
WHERE SUBSTR(ID,1,1)='P'
oRDER BY id;
QUIT;
PROC PRINT;
RUN;

Re: Finding a Specific letter in Specific position in text

anushreebiotech — Wed, 29 May 2019 17:29:48 GMT

Hi,

proc sql;
select * from test
where lower(id) like 'p%';
quit;

Regards,

Anushree

Re: Finding a Specific letter in Specific position in text

ybz12003 — Wed, 29 May 2019 17:36:05 GMT

Thank you so much!

Re: Finding a Specific letter in Specific position in text

GPatel — Wed, 29 May 2019 17:43:43 GMT

PROC SQL;
CREATE TABLE b(where=(SUBSTR(ID,1,1)='P')) AS
SELECT * FROM a
oRDER BY id;
QUIT;

PROC SQL;
CREATE TABLE b AS
SELECT * FROM a(where=(SUBSTR(ID,1,1)='P'))
oRDER BY id;
QUIT;

Re: Finding a Specific letter in Specific position in text

FreelanceReinh — Wed, 29 May 2019 18:08:51 GMT

The EQT operator is another option:

where ID eqt 'P'

Re: Finding a Specific letter in Specific position in text

ybz12003 — Wed, 29 May 2019 19:33:09 GMT

That is cool! How to express not equal?

Re: Finding a Specific letter in Specific position in text

GPatel — Wed, 29 May 2019 19:50:12 GMT

Use ^=

Re: Finding a Specific letter in Specific position in text

GPatel — Wed, 29 May 2019 19:51:06 GMT

For “not equal”to operator.

Re: Finding a Specific letter in Specific position in text

FreelanceReinh — Wed, 29 May 2019 21:26:57 GMT

@ybz12003 wrote:

That is cool! How to express not equal?

The negative operator corresponding to EQT is the NET operator ("not equal to truncated strings"; see the table I linked in the other post). So, for example, with where name net 'Ja' you could select all names from SASHELP.CLASS not starting with "Ja".

Re: Finding a Specific letter in Specific position in text

ChrisNZ — Thu, 30 May 2019 04:48:41 GMT

The first() function is the appropriate function to use here.

Re: Finding a Specific letter in Specific position in text

SimonDawson — Thu, 30 May 2019 01:52:51 GMT

I had no idea that function existed! Good suggestion.