Community Trekker

Joined:

Feb 27, 2017

## Problem Of Missing Values

Hi, everyone
I not sure i faced this problem is whether there have missing value that cause me cannot caluclate the column or not?

Now, I want calculate the rate by using the formula below in the script, but some data table consists of missing value
Because before that the data table that doesn't have missing value, I able to calculate the rate.

So, May I know how to fix the above problem that I faced.

Thanks.

Below is my script that already joining the 2 data table together.

dt_Join = dt_firstseq_summary << Join(
With(dt_final_summary),
Select( :S_B_Name_WS1, :N Rows, :Name( "Percent" ) ),
SelectWith( :S_B_Name_WS1, :N Rows, :Name( "Percent" ) ),
By Matching Columns( :S_B_Name_WS1 = :S_B_Name_WS1 ),
Drop multiples( 0, 0 ),
Include Nonmatches( 1, 1 ),
Preserve main table order( 1 ),
Output Table Name("Combined Table");
);



1 ACCEPTED SOLUTION

Accepted Solutions

Community Manager

Joined:

Jun 23, 2011

Solution

## Re: Problem Of Missing Values

I would create a new column with a formula using If() to test each of your column for missing (use the Is Missing() function) and put the value of the other column into this new column.

The formula would look something like this:

-Jeff
5 REPLIES

Community Trekker

Joined:

Jun 5, 2014

## Re: Urgent:Problem Of Missing Values

Hi, blue1994!

Your's seems like an easy question.  However, there's been a great deal of research going on about missing data problems for the better part of a century.  It's not surprising that analysts are unaware of the state-of-the-art, since the research itself is fairly impenetrable and old, deprecated techniques are very easy to implement in most software.

Imputation is the state-of-the-art.  You can reference Enders, C.K.(2010); Applied Missing Data Analysis; The Guilford Press, N.Y.  There is an accompanying website for that book with SAS and other code...and JMP can easily connect to and from SAS.  In addition, some JMP platforms allow missingness to be informative in a model, but I think that doesn't apply to your calculations.

Do you have any ideas or models about why the data are missing?  If you can assume that the data are Missing Completely At Random, life might get a little easier.  Unfortunately, it's usually easy to come up with reasons that assumption could be violated.

Community Manager

Joined:

Jun 23, 2011

## Re: Problem Of Missing Values

It's not clear exactly what you're asking. There is no way to do arithmetic on missing values so any calculation involving a missing value will result in a missing value.

Since your Join is set to include non-matches from both tables it may not be surprising that there are missing vales in the resulting table.

-Jeff

Community Trekker

Joined:

Feb 27, 2017

## Re: Problem Of Missing Values

Hi,

I have solve the above problem.
But now I have another question: Is it possible JSL able to fill up the missing value?

Thanks.

Super User

Joined:

Jun 22, 2012

## Re: Problem Of Missing Values

All data table cells are directly referencable.  The general form is

data table reference:Column Name[row number]

or

data table reference[Column Number, Row Number]    (this is JMP 13 or later)

Names Default To Here( 1 );
dt = Open( "\$SAMPLE_DATA\big class.jmp" );

// Set the value of column age in row 1, the value of the column age in row 5
dt:age[1] = dt:age[5];

The information that I provided above is one of the most basic elements of JMP.  You need to read the Scripting Guide!!!

Help==>Books==>Scripting Guide

Jim

Community Manager

Joined:

Jun 23, 2011

Solution

## Re: Problem Of Missing Values

I would create a new column with a formula using If() to test each of your column for missing (use the Is Missing() function) and put the value of the other column into this new column.

The formula would look something like this:

-Jeff