topic Re: Iterate through multiple columns and replace cells in Discussions

Iterate through multiple columns and replace cells

Ksrzg01 — Tue, 13 Jun 2017 20:16:08 GMT

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!

Re: Iterate through multiple columns and replace cells

txnelson — Tue, 13 Jun 2017 20:59:53 GMT

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

Re: Iterate through multiple columns and replace cells

Ksrzg01 — Tue, 13 Jun 2017 21:06:04 GMT

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