cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
sophiaw
Level III

JSL Selecting Columns Using Strings

Hi,

 

I'm having a problem where I have so many columns I want to select, but I don't want to type all of them out to create a new subset of them.

 

What I have is 200 columns that all start with the same string. I was wondering if there is a way I can select these columns with a loop or some command.

I have tried using the line:

 

 

cols = dt << Get Column Names(Starts With("Year"));

But if just gets every column name, and I do have a few columns that do not start with "Year" or even have "Year" in the name. Right now I'm making a new subset of the columns I want with: 

 

dt2 = dt << Subset(
                            All rows,
                            columns(
                                          :Time,
                                          :Day,
                                          :Name("Year 1"),
                                          :Name("Year 2"),
                                          :Name("Year 3"),
                                          .
                                          .
                                          .
                                          etc.
                                          ),
                                          output table name("All Years")
);

 

There are many other columns in my table, but I just want to separate out these. Please help!

1 ACCEPTED SOLUTION

Accepted Solutions
pmroz
Super User

Re: JSL Selecting Columns Using Strings

This code will do the trick.  Nice blog Dave, btw!

 

 

dt = New Table( "Year Example",
     Add Rows( 3 ),
     New Column( "ABC", Numeric, Continuous, Format( "Best", 12 ), Set Values( [1, 2, 3] ) ),
     New Column( "DEF", Numeric, Continuous, Format( "Best", 12 ), Set Values( [4, 5, 6] ) ),
     New Column( "Year 1", Numeric, Continuous, Format( "Best", 12 ), Set Values( [7, 8, 9] ) ),
     New Column( "Year 2", Numeric, Continuous, Format( "Best", 12 ), Set Values( [10, 11, 12] ) ),
     New Column( "Year 3", Numeric, Continuous, Format( "Best", 12 ), Set Values( [13, 14, 15] ) )
);
 
col_list = dt << get column names(string);
 
for (i = nitems(col_list), i > 0, i--,
     if (!contains(col_list[i], "Year"),
           remove from(col_list, i);
     );
);
 
dt << Subset(
     Output Table( "Year Subset" ),
     All rows,
     columns( eval(col_list) )
);

 

View solution in original post

3 REPLIES 3
David_Burnham
Super User (Alumni)

Re: JSL Selecting Columns Using Strings

pmroz
Super User

Re: JSL Selecting Columns Using Strings

This code will do the trick.  Nice blog Dave, btw!

 

 

dt = New Table( "Year Example",
     Add Rows( 3 ),
     New Column( "ABC", Numeric, Continuous, Format( "Best", 12 ), Set Values( [1, 2, 3] ) ),
     New Column( "DEF", Numeric, Continuous, Format( "Best", 12 ), Set Values( [4, 5, 6] ) ),
     New Column( "Year 1", Numeric, Continuous, Format( "Best", 12 ), Set Values( [7, 8, 9] ) ),
     New Column( "Year 2", Numeric, Continuous, Format( "Best", 12 ), Set Values( [10, 11, 12] ) ),
     New Column( "Year 3", Numeric, Continuous, Format( "Best", 12 ), Set Values( [13, 14, 15] ) )
);
 
col_list = dt << get column names(string);
 
for (i = nitems(col_list), i > 0, i--,
     if (!contains(col_list[i], "Year"),
           remove from(col_list, i);
     );
);
 
dt << Subset(
     Output Table( "Year Subset" ),
     All rows,
     columns( eval(col_list) )
);

 

sophiaw
Level III

Re: JSL Selecting Columns Using Strings

Thank you PMroz, that worked great!

And that is a nice blog Dave, thanks for the link!