Choose Language Hide Translation Bar
AT
AT
Community Trekker

Explore Missing values for many columns

Hi,

I like to write a script to explore missing values and when I execute it, will generate many tables for each column. Since I am  using multivariate normal imputation, all columns have to be selected simulataneously. Can you help me to achieve that? Here is the script I have so far.

 

lstNames = dt << Get Column Names(String,Numeric);
 
ncols = N items(lstNames);
 
for (i=1, i <= N items(cols), i++,
 
obj = Explore Missing Values(
Y(
Column(lstNames[i])
),
Missing Value Report,
SendToReport(
Dispatch( {}, "Missing Columns", OutlineBox, {SetHorizontal( 1 )} )
)
);

 

 

 Thanks

obj << Multivariate Normal Imputation;

 

);

0 Kudos
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
txnelson
Super User

Re: Explore Missing values for many columns

change the For loop definition from "<" to "<="

Jim

View solution in original post

0 Kudos
9 REPLIES 9
Highlighted
txnelson
Super User

Re: Explore Missing values for many columns

Here is an example of one way to solve this issue

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

lstNames = dt << Get Column Names( string, Numeric );
// Create a string variable of the structure the platform
// wants them passed to it
names = ":" || lstNames[1];
For( i = 2, i < N Items( lstNames ), i++,
	names = names || ",:" || lstNames[i]
);

// Execute the following string, with the column names embedded in it
Eval(
	Parse(
		"obj = Explore Missing Values(
		Y( " || names ||
		")) ),
		Missing Value Report,
		SendToReport( Dispatch( {}, \!"Missing Columns\!", OutlineBox, {SetHorizontal( 1 )} ) ));"
	)
);
Jim
Highlighted
AT
AT
Community Trekker

Re: Explore Missing values for many columns

Hi Jim,

Thanks so much for quick feedback and resolution. I tried your suggestions and it works perfectly. I added

 

 

obj << Multivariate Normal Imputation;

 

to do the imputation. Thanks again.

0 Kudos
Highlighted
AT
AT
Community Trekker

Re: Explore Missing values for many columns

Hi Jim,

I just noticed in the log file there is an error:

Unexpected ")". Perhaps there is a missing ";" or ",".

Line 2 Column 789: ...:P2A1,:P2B1,:PM_L1,:P1)) ►),...

 

The remaining text that was ignored was

),Missing Value Report,SendToReport(Dispatch({}","Missing Columns,OutlineBox,{

SetHorizontal(1)})));

Explore Missing Values[]

Do you know what is this related to? I noticed that it won't do the last column missing value imputation.

 

Thanks for your help.

0 Kudos
Highlighted
txnelson
Super User

Re: Explore Missing values for many columns

There were 2 ")" that were inadvertently left in the code....here is a corrected version

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

lstNames = dt << Get Column Names( string, Numeric );
// Create a string variable of the structure the platform
// wants them passed to it
names = ":" || lstNames[1];
For( i = 2, i < N Items( lstNames ), i++,
	names = names || ",:" || lstNames[i]
);

// Execute the following string, with the column names embedded in it
Eval(
	Parse(
		"obj = Explore Missing Values(
		Y( " || names ||
		")) ),
		Missing Value Report,
		SendToReport( Dispatch( {}, \!"Missing Columns\!", OutlineBox, {SetHorizontal( 1 )} ) ;"
	)
);
Jim
0 Kudos
Highlighted
AT
AT
Community Trekker

Re: Explore Missing values for many columns

Hi Jim,

 

Thanks for the updated script. It looks like I am still getting the same error.

Unexpected ")". Perhaps there is a missing ";" or ",".

Line 2 Column 4369: ...ABC)) ►),...

 

The remaining text that was ignored was

),Missing Value Report,SendToReport(Dispatch({}","Missing Columns,OutlineBox,{

SetHorizontal(1)})));

Explore Missing Values[]

 

Thanks for your further help.

0 Kudos
Highlighted
txnelson
Super User

Re: Explore Missing values for many columns

Oops, I removed the wrong pair of ")"s.  Try this:

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

lstNames = dt << Get Column Names( string, Numeric );
// Create a string variable of the structure the platform
// wants them passed to it
names = ":" || lstNames[1];
For( i = 2, i < N Items( lstNames ), i++,
	names = names || ",:" || lstNames[i]
);

// Execute the following string, with the column names embedded in it
Eval(
	Parse(
		"obj = Explore Missing Values(
		Y( " || names ||
		"),
		Missing Value Report,
		SendToReport( Dispatch( {}, \!"Missing Columns\!", OutlineBox, {SetHorizontal( 1 )} ))) ;"
	)
);
Jim
0 Kudos
Highlighted
AT
AT
Community Trekker

Re: Explore Missing values for many columns

Hi Jim,

Thanks for the updated script. It works and no error in the log. The only thing that I noticed is that the last column of the table is not included in the missing values. I tested on two different data tables and both show the same issue. Is there any reason for that?

 

I appreciate your further help. Thanks

0 Kudos
Highlighted
txnelson
Super User

Re: Explore Missing values for many columns

change the For loop definition from "<" to "<="

Jim

View solution in original post

0 Kudos
Highlighted
AT
AT
Community Trekker

Re: Explore Missing values for many columns

Thanks Jim for quick solution. It works now.

0 Kudos