Select where and subset based on cell value

[#1]

Hi,

I have the below piece of code where I want it to get column names from the file and then create a new subset table followed by finding mean and SD of all numeric columns. The Mean and SD code works but it is not creating the subset table at all. Any ideas? I have checked this against all posted discussions. I have tried both Starts with and Contains conditions

Here is the sample file:

Serial Num	Code	Area	Height	Weight	Age
1	55414	Minneapolis N
2	55455	Minneapolis S
3	4446804e	Minneapolis E
4	4389568	Minneapolis W
5	g58934	St. Paul N
6	4989034	St. Paul E
7	3496f	St. Paul S
8	5404390sfd	Minneapolis E
9	43960	Minneapolis N
10	569043	St. Paul N
11	6590549	St. Paul E

dt3 = Open( "file_28.jmp" );
colcmp = "Area";
col = dt3 << get column names( string );
nc = N Items( col );
Show( nc );


For( j = 1, j <= nc, j++,
    colname = Column( j ) << Get Name;
    Show( colname );
    If( Contains( colname, colcmp ),
        dt3 << Select Where( Starts With( :colname, "Minneapolis" ) );
        dt3 << Subset( Output Table Name( "MPLS" ), Selected Rows( 1 ), Selected columns only( 0 ) );
        dt3 << Select Where( Starts With( :colname, "St. Paul" ) );
        dt3 << Subset( Output Table Name( "STP" ), Selected Rows( 1 ), Selected columns only( 0 ) );
    );
);

dtPM = Data Table( "MPLS" );

a = "Numeric";
colPM = dtPM << get column names( string );
ncPM = N Items( colPM );
Show( ncPM );

For( k = 1, k <= ncPM, k++,
    colname = Column( k ) << Get Name;
    Show( colname );
    which = dtPM:colname << Get Data Type;
    Show( which );
    If( Contains( which, a ),
        count = count + 1;
        colnames2[count] = colname || "_MPLS";
        Mean1[count] = Col Mean( :Column( k ) );
        Std1[count] = Col Std Dev( :Column( k ) );
        Show( Mean1[count] );
    );
);

[#2 · Accepted solution]

You are telling JMP to find a column named :colname.   In this reference, JMP will not evaluate the column reference beyond :colname.  What you need to do, is to specify the name in an As Column() function.  Then it will work

For( j = 3, j <= nc, j++,
	colname = Column( j ) << Get Name;
	Show( colname );
	If( Contains( colname, colcmp ),
		dt3 << Select Where( Starts With( as column(colname), "Minneapolis" ) );
		dt3 << Subset( Output Table Name( "MPLS" ), Selected Rows( 1 ), Selected columns only( 0 ) );
		dt3 << Select Where( Starts With( as column(colname), "St. Paul" ) );
		dt3 << Subset( Output Table Name( "STP" ), Selected Rows( 1 ), Selected columns only( 0 ) );
	);
);

[#3]

Thank you Jim. I was having confusion on how to pass variables that store column names to functions and could'nt really find examples. This cleared the error.