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));
);
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
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
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));
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.
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));
);
See my corrected program above
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 ) );
);