script to find and replace a numeric value with a missing value in the whole table

[#1]

Hi,

I would like a code that would find and replace the number 999 in the whole table into a missing value.

TIA

[#2 · Accepted solution]

Hi @eliyahu100 ,

  This code should do it. If the value is not 999, then you can change it to whichever value it needs to check.

Names Default To Here( 1 );

dt = Current Data Table();

For( i = 1, i <= N Rows( dt ), i++,
	For( l = 1, l <= N Cols( dt ), l++,
		If( Column( l )[i] == 999,
			Column( l )[i] = .
		)
	)
);

Hope this does it!,

DS

[#3 · Accepted solution]

Thanks for the code.

It worked fine on the numeric col's but for those who are char I added another loop:

For( i = 1, i <= N Rows( dt1 ), i++,
	For( l = 1, l <= N Cols( dt1 ), l++,
		If( Column( l )[i] == "999",
			Column( l )[i] = ""
		)
	)
);

All the best

[#4]

Hi @eliyahu100 ,

  You should be able to keep it to a single loop by including another test in the For loop that tests the column data or modeling type, and if == to "continuous" or "numerical", then it would perform the later actions of replacing the 999 with NaN.

  A very good resource that contains all the commands in JMP is the Scripting Index: Help > Scripting Index. You can type in the command (or as close to what you think it might be) and it will give suggestions, when you find the right command, there's always an example script to help show how that command is used. Very helpful and useful resource.

DS

[#5]

You can interactively replace the code 999 with missing using the Cols > Recode command. See the JMP Help for an explanation of Recode. It might not be the best choice for this case, but it is very powerful and often easier that writing a script.

[#6]

Hi @eliyahu100,

You already have a good answer for how to actually replace the values, but another option to consider is to use Missing Value Coding so that JMP treats those 999 values as missing. This will be faster for a table of any size, and preserves those missing value codes in your table (but will not use them in calculations or graphs). Below is some JSL to accomplish that, or you can set this property interactively:

For( i = 1, i <= N Cols( dt ), i++,
	Column(dt,i) << Set Property( "Missing Value Codes", {999} )
);

I hope this helps!

@julian