Finding a Specific letter in Specific position in text

[ybz12003]

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;

[GPatel · Accepted solution]

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;

[anushreebiotech]

Hi,

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

Regards,

Anushree

[GPatel]

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;

[ybz12003]

Thank you so much!

[FreelanceReinh]

The EQT operator is another option:

where ID eqt 'P'

[ybz12003]

That is cool!  How to express not equal?

[FreelanceReinh]

---

@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".

[GPatel]

Use ^=

[GPatel]

For “not equal”to operator.

[ChrisNZ]

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

[SimonDawson]

I had no idea that function existed! Good suggestion.