SAS SUPPORT

[#1]

Hello All

[#2]

You don't need Regex complexity, SAS function FINDW is enough.

You might not need SAS at all to do this. You could create a column in Excel with formula =ISERROR(FIND("BIRD",UPCASE(A1))) and filter on the values (keep FALSE), copy and paste the resulting lines.

PG

[#3]

But if you want to use regular expressions, here is an illustration :

data test;
length txt $64;
input txt &;
datalines;
bird
BIRD at beginning
a birdy
not a bird
Must be BigBird
just a test
;

data _null_;
set test;
mt = prxmatch("/\bbird\b/i", txt);
put mt txt;
run;

The regular expression translates as : \b : word boundary, bird : the keyword to search, \b : another word boundary, i : make the match case insensitive.

PG

[#4]

This should do what you want in JSL:

New Table( "Birds",

    Add Rows( 5 ),

    New Column( "Column 1",

        Character,

        Nominal,

        Set Values(

            {"Angry bird", "Cute cat", "Bad dog", "Happy Bird", "UPPERCASE BIRD"}

        )

    ),

    New Column( "Column 2",

        Numeric,

        Continuous,

        Format( "Best", 12 ),

        Set Values( [1, 2, 3, 4, 5] )

    ),

    Set Row States( [1, 0, 0, 0, 0] )

);

dt = data table("Birds");

match_rows = dt << get rows where(contains(uppercase(:Column 1), "BIRD"));

newdt = dt << subset(columns(Column 1, Column 2), rows(match_rows));

[#5]

You can ignore the New Table portion of my post - that's just to create a table of dummy data.  Focus on the last 3 lines.  You'll need to change the column names I used to the ones in your spreadsheet.

You will need to read in your Excel spreadsheet via File > Open.  Then run the last 3 lines of my little program, with column names modified for your table.

[#6 · Accepted solution]

This will do it:

teb_text = "";

dt = current data table();

column_name_list = dt << get column names(String);

nw = new window("Find Rows and Subset", << modal(),

    panelbox("Select column to search",

        lb1 = listbox(column_name_list, max selected(1),

            col_to_search_list = lb1 << get selected;

            col_to_search = col_to_search_list[1];

        ),

    ),

    panelbox("Enter text to search for",

        teb = text edit box(teb_text)

    ),

    panelbox("Actions",

        hlistbox(

            buttonbox("OK", ok_button = 1;

                            teb_text = teb << get text;

            ),

            buttonbox("Cancel", ok_button = 0),

        )

    )

);

if (ok_button,

    match_expr = evalinsert(

"\[match_rows = dt << get rows where(contains(uppercase(:^col_to_search^), uppercase(teb_text)))]\");

      parse(eval(match_expr));

    newdt = dt << subset(rows(match_rows));

);

[#7]

See my corrected program above

[#8]

Should be eval(parse()), not parse(eval()).  Corrected code below:

teb_text = "";

dt = Current Data Table();

column_name_list = dt << get column names( String );

nw = New Window( "Find Rows and Subset",

    <<modal(),

    Panel Box( "Select column to search",

        lb1 = List Box(

            column_name_list,

            max selected( 1 ),

            col_to_search_list = lb1 << get selected;

            col_to_search = col_to_search_list[1];

        ),

    ),

    Panel Box( "Enter text to search for", teb = Text Edit Box( teb_text ) ),

    Panel Box( "Actions",

        H List Box(

            Button Box( "OK",

                ok_button = 1;

                teb_text = teb << get text;

            ),

            Button Box( "Cancel", ok_button = 0 ),

        )

    )

);

If( ok_button,

    match_expr = Eval Insert(

        "\[match_rows = dt << get rows where(contains(uppercase(:^col_to_search^), uppercase("^teb_text^")))]\"

    );

    Eval( parse(match_expr ) );

    newdt = dt << subset( rows( match_rows ) );

);