How to find specific rows start with certain letter in Snowflake

Question

My input table:

Customer	Records
0001	YNNNYNYNNNNN
0002	NNNNNNYNNNNN
0003	YYNNYNYNYYNN
0004	NYNNYNYNNNNY

The goal to find all rows with records start with letter Y as:

Customer	Records
0001	YNNNYNYNNNNN
0003	YYNNYNYNYYNN

Answer 1

I see that this has a SAS tag, so here are a few SAS solutions.

proc sql;
    create table want as
        select *
        from snow.have
        where records LIKE 'Y%'
    ;
quit;

A data step will also work:

data want;
    set snow.have;
    where records =: 'Y';
run;

Or:

data want;
    set snow.have;
    where substr(records, 1, 1) = 'Y';
run;

Just check that your functions are compatible, or SAS will drag everything in the database to the SAS server before running the code. Here is a list of supported SAS functions in Snowflake.

To confirm that it's not doing this, use the following sastrace options:

options sastrace=',,,d' sastraceloc=saslog;

If you see the note below in the SAS log, then the function is not compatible. It will pass what it can to the DB, then do the rest.

ACCESS ENGINE: SQL statement was not passed to the DBMS, SAS will do the processing.

To avoid this entirely, you can also run SQL directly in Snowflake using the SQL pass-through facility.