534368_en-US

liliconcom

How to script an aggregate column from a variable list of column references

I have a list of columns that I need to aggregate automatically. Currently, what I'm trying is something like this, which isn't yielding any results.

cols = {:col1, :col2, :col3}

Eval(Eval Expr(
  dt << New Column(
    Formula(Minimum(Expr(cols))
  )
)

I've also tried generating this interactively and saving the column formula. It works, but gives me 

Minimum(:col1, :col2, :col3)

which I'm not sure how to re-create in JSL.

Sharing and Communicating ResultsRe: How to script an aggregate column from a variable list of column references

Here are two methods you might try:

Names Default to Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
cols = dt << Get Column Names( "String" );
col list = {};
For Each( {col}, cols,
 If( Ends With( col, "t" ), Insert Into( col list, As Name( col ) ) )
);
Eval( Eval Expr(
dt << New Column( "NEW_COL", "Numeric", <<SET formula=""></SET>

Names Default to Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
cols = dt << Get Column Names( "String" );
col list = Expr( Min() );
For Each( {col}, cols,
 If( Ends With( col, "t" ), Insert Into( col list, As Name( col ) ) )
);
Eval( Eval Expr(
dt << New Column( "NEW_COL", "Numeric", <<SET formula=""></SET>

Re: How to script an aggregate column from a variable list of column references

I am guessing on what your initial column names list looks like, but if it is just an unquoted list o column names, the below script is an example of onw way to process it

Names Default To Here( 1 );
dt = 
// Open Data Table: Big Class.jmp
// → Data Table( "Big Class" )
Open( "/C:/Program Files/SAS/JMPPRO/15/Samples/Data/Big Class.jmp" );

colNames = dt << get column names;
theList = {};
For Each( {col}, colNames,
 Show( col );
 If( As Column( col ) << get data type == "Numeric",
  Insert Into( theList, As Column( col ) << get name )
 );
);

theString = Concat Items( theList, ",:" );

Eval( Parse( "dt << new column(\!"test\!",formula(minimum(:" || thestring || ")));" ) );

Re: How to script an aggregate column from a variable list of column references

My actual list is defined through regex matching based on user input. The script iterates through the data table's list of column names and inserts matching ones into a list.

For Each ({col}, colNames,
  If (condition... Insert Into(list, :Column(col))
)

I tried changing the syntax to :(Column(col)) but that resulted in a list of missing items rather than what I had previously been getting ({Column(col1), Column(col2), ...}). Same goes for As Column(col).

I'm using JMP 16.

Re: How to script an aggregate column from a variable list of column references

Please check your list definition and make sure the ":" is specified in front of each column name.

 

What version of JMP are you using?

Re: How to script an aggregate column from a variable list of column references

Thanks for the quick response! I tried this, but it seems to leave the column formula unresolved (as Minimum(Eval(cols)) ).

Re: How to script an aggregate column from a variable list of column references

Hi,

Try this syntax:

Names default to Here (1);

dt = Current Data Table ();

cols = {:col1, :col2, :col3};

dt << New Column("TEST", numerical, continuous, << Formula(Minimum(Eval(cols))));

Best,

TS

Re: How to script an aggregate column from a variable list of column references

This might give some ideas Using list of columns in formulas 

View more...