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
Kriti
Level I

Select where and subset based on cell value

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] );
    );
);

 

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: Select where and subset based on cell value

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 ) );
	);
);

 

Jim

View solution in original post

2 REPLIES 2
txnelson
Super User

Re: Select where and subset based on cell value

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 ) );
	);
);

 

Jim
Kriti
Level I

Re: Select where and subset based on cell value

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.