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

Iterate through multiple columns and replace cells

Hello everyone,

 

I'm trying to iterate through multiple tables and replace any value >6 sigma with a ".". Below is my attempt to iterate through big class and replace the weight and height values greater than 1 sigma with a .; however, it is not working as intended. I think I'm confusing myself with the two strings. 

 

Names Default To Here( 1 );
Clear Globals();



dt = Open("...\SAS\JMP\13\Samples\Data\Big Class.jmp");


mycol = J( N Rows( dt ), 1 );
list_col_names = dt << Get Column Names( String );


For( i = 1, i <= N Items( list_col_names ), i++, 

	cur_col = (list_col_names[i]);
	
	
	If( Left( cur_col,1 ) == "ei", 
	
		mycol = dt << get as matrix( cur_col );
			
		q25 = Quantile( 0.25, mycol );
		q50 = Quantile( 0.50, mycol );
		q75 = Quantile( 0.75, mycol );
		
		mycol1 = ((mycol - q50) / ((q75 - q25) / 1.35));
		
		For( r = 1, r <= N Rows( dt ), r++,
			If(
				mycol1[r] > 1, mycol1[r] = .,
				mycol1[r] < -1, mycol1[r] = .
			));
		
	);
);



Thanks!

 

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: Iterate through multiple columns and replace cells

The main issues that I am seeing are that you did not identify the Hieght and Weight columns and that once you found the rows above or below 1 sigma, the values were not written back to the data table.

Here is a modification to your code that corrects those issues.

Names Default To Here( 1 );
Clear Globals();

dt = Open("$SAMPLE_DATA\Big Class.jmp");

mycol = J( N Rows( dt ), 1 );
list_col_names = dt << Get Column Names( String );

For( i = 4, i <= N Items( list_col_names ), i++, 
	cur_col = (list_col_names[i]);	
	If( Contains(cur_col,"ei") > 0, 	
		mycol = dt << get as matrix( cur_col );			
		q25 = Quantile( 0.25, mycol );
		q50 = Quantile( 0.50, mycol );
		q75 = Quantile( 0.75, mycol );		
		mycol1 = ((mycol - q50) / ((q75 - q25) / 1.35));
		
		For( r = 1, r <= N Rows( dt ), r++,
			If(
				mycol1[r] > 1, column(dt,cur_col)[r] = .,
				mycol1[r] < -1, column(dt,cur_col)[r] = .
			));
				
	);
);
Jim

View solution in original post

2 REPLIES 2
txnelson
Super User

Re: Iterate through multiple columns and replace cells

The main issues that I am seeing are that you did not identify the Hieght and Weight columns and that once you found the rows above or below 1 sigma, the values were not written back to the data table.

Here is a modification to your code that corrects those issues.

Names Default To Here( 1 );
Clear Globals();

dt = Open("$SAMPLE_DATA\Big Class.jmp");

mycol = J( N Rows( dt ), 1 );
list_col_names = dt << Get Column Names( String );

For( i = 4, i <= N Items( list_col_names ), i++, 
	cur_col = (list_col_names[i]);	
	If( Contains(cur_col,"ei") > 0, 	
		mycol = dt << get as matrix( cur_col );			
		q25 = Quantile( 0.25, mycol );
		q50 = Quantile( 0.50, mycol );
		q75 = Quantile( 0.75, mycol );		
		mycol1 = ((mycol - q50) / ((q75 - q25) / 1.35));
		
		For( r = 1, r <= N Rows( dt ), r++,
			If(
				mycol1[r] > 1, column(dt,cur_col)[r] = .,
				mycol1[r] < -1, column(dt,cur_col)[r] = .
			));
				
	);
);
Jim
Ksrzg01
Level I

Re: Iterate through multiple columns and replace cells

Thats where my issue was, it works great! Thank you for your help!