Insert 0 if missing Data

[#1]

Hello Community,

I searched for, but I can't find a skip in the Community.

I Looking for a Script, which simply make out of all missing Datas in my data table a "0" (Zero).

Thanks in advance

Andrea

[#2 · Accepted solution]

There are many posts regarding this in the community, here are few (I would suggest checking them in order)

1. Replace missing values with 0 
2. How do I change a dot to number zero 
3. Should you Loop through a data table or use Recode, or use Get Rows Where to change values in a data...

Names Default To Here(1);

dt = Current Data Table();

nc = dt << get column names(Numeric);
mat = dt[0, nc];
mat[Loc(mat, .)] = 0;
dt[0, nc] = mat;

Write();

[#3]

Good morning,

You have two ways to achieve this sort of thing.

The first would be to use a script like this (takes into account numeric columns):

dt = current data table();

nc = dt << get column names( Numeric );
For( i = 1, i <= 18, i++,
	Column( nc[i] )[dt << get rows where( Is Missing( As Column( nc[i] ) ) )] = 0
);

//the end of the loop is the number of your numeric columns

Or if you want to do it for a particular column:

Names default to here(1);

// Ouvrir la table de données : Missing Data Pattern.jmp
dt = Open( "$SAMPLE_DATA/Missing Data Pattern.jmp" );


// Nouvelle colonne : Colonne 5
dt << New Column( "TEST",
	Numeric,
	"Continuous",
	Format( "Best", 12 ),
	Set Formula( If( Is Missing( :Trial 4 ), :TEST = 0, :TEST = :Trial 4 ) );
);

[#4]

Here is one way of handling it

Names Default To Here( 1 );
dt = Current Data Table();
For( i = 1, i <= N Cols( dt ), i++,
	If( Column( dt, i ) << get data type == "Numeric",
		Column( dt, i )[Loc( dt[0, i], . )] = 0
	)
);

This example works, but Jarmo's response is best.

[#5]

Without regard to the mechanics of changing missing values in a JMP data table from 'missing' to a numeric zero, you do realize that this change will make a huge difference in whatever analysis or data visualization technique you use in JMP for any variables in the data table where this change has been implemented? I can think of many situations where changing the value from 'missing' to a numeric zero would be a very improper thing to do. Without context of your reasons behind wanting to make this change...I just wanted to possibly alert you or others reading this thread that this type of change could be a very bad idea.