topic Re: Script to export AIC into new data table in Discussions

Script to export AIC into new data table

jpmontagne — Tue, 07 Oct 2014 21:03:11 GMT

I am comparing AIC results for multiple models in the Fit Parametric Survival platform. At this point, I have a script to run all the models with produces multiple windows, and I and copy/pasting the AIC and other outputs into an excel file one by one. Needless to day, this is a time consuming & wasting process. I'm a novice in JSL, would anybody be able to help me produce an automated process to extract AIC and other information from the multiple report windows and save them into a single data window? The additional information I'm evaluating is parameter convergence, observations used, and # of censored & uncensored values.

Thanks for any help!

Re: Script to export AIC into new data table

Mark_Bailey — Wed, 08 Oct 2014 15:04:43 GMT

You can interactively produce this result by right-clicking on the table with the AIC value in your report and select Make Combined Data Table.

The scripting equivalent requires sending a message with the same name as the menu command to the display box object that is responsible for presenting the AIC value in the report. Use the following example for this idea:

open Big Class
launch the Bivariate platform with height versus weight and sex as a grouping variable
fit a line in both groups
get the reference to the report layer
repeatedly subscript the display tree in the report to the level of the first table (e.g., parameter estimates)
send the message

dt = Open( "$SAMPLE_DATA/Big Class.JMP" );

biv = dt << Bivariate(

Y( :height ),

X( :weight ),

Fit Line,

By( :sex )

);

biv4 = biv << Report;

bivr[1]["Parameter Estimates"][TableBox(1)] << Make Combined Data Table;

Re: Script to export AIC into new data table

jpmontagne — Tue, 18 Oct 2016 21:48:39 GMT

Thanks for your quick reply, Mark!! That was very helpful and educational, although at first it didn't produce the data table until I rewrote the last bit as

Report( biv[1] )["Parameter Estimates"][TableBox(1)] << Make Combined Data Table;

by following this example: 46983 - How to make a combined data table of Summary Statistics from a Distribution report when using a By variable.

However, I am still unable translate it into my work flow, maybe because the table I want has no title (e.g. "Parameter Estimates"). Below is an example of one report. The circled portion is what I want to make into a data table. I've included a sample of the script I'm using at the bottom. The challenge is how to add the circled data from ~100 reports generated at once into one data table. I've added a screenshot of the final table I'm looking for, that I'm currently entering manually into Excel (yuck). I would also need a way to add the model # (e.g. 633) to each row.

Thanks again for your help!

Here's an example of the script I'm using:

Fit Model(

Censor( :Dispersed),

Censor Code( "0" ),

Y( :days to Dispersal ),

Effects( :Weight,:Year,:Source,:Translocation,:Treatment,:Geology,:Site,),

Personality( Parametric Survival ),

Distribution( Frechet ),

Run( Likelihood Ratio Tests( 1 ) ),

SendToReport(

Dispatch(

{},

Parametric Survival Fit,

OutlineBox,

{Set Title( "Parametric Survival Fit - Model 632 " )}

)

);

Fit Model(

Censor( :Dispersed),

Censor Code( "0" ),

Y( :days to Dispersal ),

Effects( :Weight,:Year,:Source,:Translocation,:Treatment,:Geology,),

Personality( Parametric Survival ),

Distribution( Frechet ),

Run( Likelihood Ratio Tests( 1 ) ),

SendToReport(

Dispatch(

{},

Parametric Survival Fit,

OutlineBox,

{Set Title( "Parametric Survival Fit - Model 633 " )}

)

);

Re: Script to export AIC into new data table

ian_jmp — Thu, 09 Oct 2014 11:25:00 GMT

Building on what Mark has explained, here's one way:

dt = Open("$SAMPLE_DATA/Reliability/Tobit2.jmp");

// Add a random 'by' column

byCol = dt << NewColumn("By", Numeric, Nominal, Formula(RandomInteger(1, 2)));

// Make the invisible report

fm = dt << Fit Model(

Y( :YLow, :YHigh ),

Effects( :age, :liquidity ),

By(:By),

Personality( Parametric Survival ),

Distribution( LogNormal ),

Run( Likelihood Ratio Tests( 1 ) ),

Invisible

);

// Get a reference to the report layer

fmrep = fm << Report;

// This will be a list, one item per 'by' group

outlineTitle = fmRep << GetTitle;

// Make an empty display box

lub = LineUpBox(NCol(1));

// Add the content we need

For(o=1, o<=NItems(outlineTitle), o++,

lub << Append(

OutlineBox(outlineTitle[o],

(fmRep[o])[TableBox(1)];

);

Close(dt, NoSave);

// Make a Journal

NewWindow("Report", << Journal, lub);

The only difficulty is to know which table boxes to include. To figure this out, right click on the triangle of the outline node, then select Edit > Show Tree Structure.

Re: Script to export AIC into new data table

ian_jmp — Thu, 09 Oct 2014 11:35:14 GMT

Ooops! I see you wanted a table, not a journal. In which case 'Make Combined Data Table' is probably more direct.

Re: Script to export AIC into new data table

Mark_Bailey — Thu, 09 Oct 2014 17:30:56 GMT

Where I used "Parameter Estimates" in the subscript to target a particular outline box, you would instead use "Parametric Survival Fit - Model 633 ".

Re: Script to export AIC into new data table

jpmontagne — Fri, 10 Oct 2014 17:40:27 GMT

Mark & Ian, thank you for your time! I'm still not getting this right though, because my version of the script below still won't produce a table. I've tried combining your two examples:

dt = Open( "$SAMPLE_DATA/Big Class.JMP" );

biv = dt << Bivariate(

Y( :height ),

X( :weight ),

Fit Line,

By( :sex )

);

fmrep = fm << Report;

fmrep[1]["Parameter Estimates"][TableBox(1)] << Make Combined Data Table;

Any ideas?

Thank you,

Re: Script to export AIC into new data table

Jeff_Perkinson — Fri, 10 Oct 2014 18:42:04 GMT

You've got some subscripting errors in this line:


fmrep[1]["Parameter Estimates"][TableBox(1)] << Make Combined Data Table;

Instead it should be:

fmrep[Outlinebox("Parametric ?")][TableBox(1)]<<make combined data table;

You could use Outlinebox(1) or put the full title in there but the wildcard "?" makes it a little more general.

-Jeff

Re: Script to export AIC into new data table

jpmontagne — Fri, 10 Oct 2014 22:19:05 GMT

Alright, so we're getting closer. Even though I pasted the wrong script, Jeff gave me the right answer. The following script provides the desired table for the sample data:

dt = Open("$SAMPLE_DATA/Reliability/Tobit2.jmp");

// Add a random 'by' column

byCol = dt << NewColumn("By", Numeric, Nominal, Formula(RandomInteger(1, 2)));

// Make the invisible report

fm = dt << Fit Model(

Y( :YLow, :YHigh ),

Effects( :age, :liquidity ),

By(:By),

Personality( Parametric Survival ),

Distribution( LogNormal ),

Run( Likelihood Ratio Tests( 1 ) ),

);

// Get a reference to the report layer

fmrep = fm << Report;

fmrep[1][Outlinebox("Parametric ?")][TableBox(1)]<<make combined data table;

Close(dt, NoSave);

// This script works the way it's supposed to!!

But why is it that when I remove byCol = dt << NewColumn(...) and By(:By),the script no longer produces a table?

And since my goal is to create a single combined data table from multiple reports, how do I get the following sample script to work:

dt = Open("$SAMPLE_DATA/Reliability/Tobit2.jmp");

// Make the invisible report

fm1 = dt << Fit Model(

Y( :YLow, :YHigh ),

Effects( :age, ),

Personality( Parametric Survival ),

Distribution( LogNormal ),

Run( Likelihood Ratio Tests( 1 ) ),

);

fm2 = dt << Fit Model(

Y( :YLow, :YHigh ),

Effects( :liquidity ),

Personality( Parametric Survival ),

Distribution( LogNormal ),

Run( Likelihood Ratio Tests( 1 ) ),

);

fm3 = dt << Fit Model(

Y( :YLow, :YHigh ),

Effects( :age, :liquidity ),

Personality( Parametric Survival ),

Distribution( LogNormal ),

Run( Likelihood Ratio Tests( 1 ) ),

);

// Get a reference to the report layer

fmrep1 = fm1 << Report;

fmrep1 [1][Outlinebox("Parametric ?")][TableBox(1)]<<make combined data table;

THANKS EVERYBODY!!!

Re: Script to export AIC into new data table

Jeff_Perkinson — Fri, 10 Oct 2014 23:17:38 GMT

When you use a By() in a platform launch JMP will return a list of the resulting platforms. Without it, JMP will return a single platform, not in a list.

Since the examples here used By() they needed to index into the list of platforms to reference a specific one for one by group. That's the [1] in:

fmrep1[1][Outlinebox("Parametric ?")][TableBox(1)]<<make combined data table;

You're not using By() in any of your platform launches, so you don't need the [1].

To get all of the tables into a single data table, it would be easiest to start off with all the reports in a single window.

You can do that using New Window() to create your own report window.


dt = Open( "$SAMPLE_DATA\Reliability\Tobit2.jmp" );


myWin = New Window( "My Window",

  fm1 = dt << Fit Model(

  Y( :YLow, :YHigh ),

  Effects( :age, ),

  Personality( Parametric Survival ),

  Distribution( LogNormal ),

  Run( Likelihood Ratio Tests( 1 ) ), 


  );


  fm2 = dt << Fit Model(

  Y( :YLow, :YHigh ),

  Effects( :liquidity ),

  Personality( Parametric Survival ),

  Distribution( LogNormal ),

  Run( Likelihood Ratio Tests( 1 ) ), 


  );


  fm3 = dt << Fit Model(

  Y( :YLow, :YHigh ),

  Effects( :age, :liquidity ),

  Personality( Parametric Survival ),

  Distribution( LogNormal ),

  Run( Likelihood Ratio Tests( 1 ) ), 


  );

);


combined = myWin[Outline Box( "Parametric ?" )][Table Box( 1 )] << make combined data table;

Notice, that we don't need to get the report object, since we have a reference to the report window, myWin, already.

To get a table similar what you show in your original post you'll need to Split() the table you get from make combined data table.


combined <<Split(

  Split By( :Column 1 ),

  Split( :Column 2 ),

  Remaining Columns( Drop All )

);

I know that you also want a column to indicate which model the rows represent. Depending on how you're deciding which models to run, it may be easier to keep that list yourself and add a column to the data table with that information yourself.

If you don't know what models you specified, it's possible to extract that from the parametric survival reports but it's not as easy.

-Jeff

Re: Script to export AIC into new data table

jpmontagne — Sat, 11 Oct 2014 05:38:55 GMT

EUREKA!!! Thanks Jeff, Mark and Ian! You guys taught me a lot in a short time, and gave me the solution that will save me hours of unnecessary click,copy,pasting!

Now, if any of you know how to add a column for the warning text when models don't converge, that would be icing on the cake. But I'm happy with what we've accomplished!

Using VBA Macro to import data to JMP and return JMP results back to Excel sheet on Weibull Analysis

jwong7 — Fri, 13 Jan 2017 08:37:59 GMT

Hi guys,

First of all i would like to thank you for sharing your scripting findings and it helped me to be able to automate my data query process. i am using VBA-Macro excel to copy my table into JMP to perform the Weibull analysis and obtain the Scale & Shape values and copy out into my VBA -Macro excel file.Below are the code i used. Hope it will be helpful for others as well.

VBA-Macro Excel.

Sub JMP()
Dim app As JMP.Application
Dim doc As JMP.Document
Set app = CreateObject("JMP.Application")
app.Visible = True
Set doc = app.OpenDocument("C:\Users\ABC\Desktop\WS.jsl")

end sub

JSL scripts ( saved as WS.jsl format)

//! <<<if you want to automatically run the JSL scripts below and it must be at your 1st line in your JSL file>>>.

dt = Open( "C:\Users\ABC\Desktop\Book1.xlsm" ); <<< JSL will open the excel file into JMP data table to execute the weibull analysis below>>>
fm = Life Distribution(
Y( :Name( "TTF,Hr" ) ),
Censor( :Censor ),
Censor Code( 1 ),
Freq( :Freq ),
<<Fit Weibull,
Confidence Interval Method( Likelihood ),
<<Set Scale( Nonparametric ),
<<Suppress Plot( Nonparametric ),
Interval Type( Simultaneous ),
<<Set Scriptables(
{Probability Paper( Profiler( 1, Confidence Intervals( 1 ), Term Value( Name( "TTF,Hr" )(4846.6174375, Lock( 0 ), Show( 1 )) ) ) ),
Weibull Distribution( Profiler( 1, Confidence Intervals( 1 ), Term Value( Name( "TTF,Hr" )(4846.6, Lock( 0 ), Show( 1 )) ) ) ),
Weibull Quantile( Profiler( 1, Confidence Intervals( 1 ), Term Value( Probability( 0.5, Lock( 0 ), Show( 1 ) ) ) ) ),
Weibull Hazard( Profiler( 1, Confidence Intervals( 1 ), Term Value( Name( "TTF,Hr" )(4846.6, Lock( 0 ), Show( 1 )) ) ) ),
Weibull Density( Profiler( 1, Term Value( Name( "TTF,Hr" )(4846.6, Lock( 0 ), Show( 1 )) ) ) ), Custom Estimation( Weibull, 0 )}
),
SendToReport(
Dispatch( {"Compare Distributions"}, "Life Distribution", FrameBox, {Grid Line Order( 2 ), Reference Line Order( 3 )} ),
Dispatch( {"Statistics", "Model Comparisons"}, "", TableBox, {Sort By Column( 2, 1 )} )
)
);

// To automatically copy out the Parametic Estimate data into new data table (eg: untitled.jmp format)
fmrep = fm << Report;
fmrep[Outline Box("Parametric Estimate ?" )][Table Box( 1 )] << make combined data table;
dt1 = Current Data Table();
dt1 << SaveAs( "c:\Users\ABC\Desktop\AAA.xlsx" ); // to save the untitled.jmp file as excel file ( in this case i naed it as AAA.xlsx) at desktop location.
Close( dt, No Save );
Close( dt1, No Save );

The above scripts is working perfectly for me.

Just thought to contribute back to this community with my recent finding & learning.

Thanks!

Jim