topic Re: script to find and replace a numeric value with a missing value in the whole table in Discussions

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

eliyahu100 — Fri, 09 Jun 2023 19:45:44 GMT

Hi,

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

TIA

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

SDF1 — Thu, 29 Apr 2021 18:56:49 GMT

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!,

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

Mark_Bailey — Thu, 29 Apr 2021 19:23:32 GMT

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.

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

eliyahu100 — Thu, 29 Apr 2021 19:57:56 GMT

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

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

SDF1 — Fri, 30 Apr 2021 12:44:33 GMT

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.

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

jules — Wed, 31 May 2023 15:16:25 GMT

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!

@jules