Hello All
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;birdBIRD at beginninga birdynot a birdMust be BigBirdjust 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.
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.
