Using list of columns in formulas

jthi — Fri, 09 Jun 2023 19:53:36 GMT

I ran into an "problem" when trying to directly use a column list returned from the column dialog (for example {:sex, :age: name}). When I try to use that list directly in Col formulas (Col Mean, Col Sum...) with byVars the formula won't work correctly. Using the list with for example Sum() formula works fine. Is this intended behaviour and does anyone have cleaner ways to build Col Sums from list of columns than the examples I have below?

Below is the example script with Sum() and ColSum() formulas. There are couple of different ways I tried to build the ColSum() formulas but managed to get them working only with Insert Into with expressions and EvalParse (which I will try to avoid due to hassle with debugging).

Example:

Names Default To Here(1);
dt = Open("$SAMPLE_DATA/Big Class.jmp");

dt << New Column("Sum", Numeric, "Continuous", Format("Best", 12), Formula(Sum(:height, :weight)));
listOfCols = {:height, :weight}; //list works here
dt << New Column("SumList", <<Formula(Eval(Eval Expr(Sum(Expr(listOfCols))))));

dt << New Column("ColSum", Numeric, "Continuous", Format("Best", 12), Formula(Col Sum(:height, :age, :sex)));
listOfCols = {:height, :age, :sex}; //won't work here
dt << New Column("ColSumList", <<Formula(Eval(Eval Expr(Col Mean(Expr(listOfCols))))));

//didnt work either
dt << New Column("ColSumList2", <<Formula(Eval(Eval Expr(Col Mean(Expr(listOfCols[1]), Expr(listOfCols[2::3]))))));

Show(dt:sum << get formula);
Show(dt:sumlist << get formula);
Show(dt:colsum << get formula);
Show(dt:colsumlist << get formula);
Show(dt:colsumlist2 << get formula);

//Insert Into with expressions seems to work, currently best option
formulaExpr = Expr(Col Sum());
For Each({value}, listOfCols, Insert Into(formulaExpr, Name Expr(value)));
dt << new column("InsertIntoColSum", << Formula(Eval(EvalExpr(formulaExpr))));
Show(dt:InsertIntoColSum << get formula);

//I could always EvilParse, but trying to avoid it if possible
Eval(Parse("dt << New Column(\!"EvilParseColSumList\!", <<Formula(Col Sum(" ||Substitute(char(listOfCols), {"{", "}"}, "") ||")))"));
Show(dt:EvilParseColSumList << get formula);

Re: Using list of columns in formulas

SDF1 — Thu, 29 Jul 2021 16:14:27 GMT

Hi @jthi ,

Not sure if this is intended or not, but it's definitely related to the list brackets, {}, and number of items in the list. I could reproduce the same issues you described.

I think your solution to do the Insert Into is the best choice. The Parse can get very complicated and difficult. I think you have a good solution with the Insert Into and best to stick with that in order to get around the list bracket issue.

Re: Using list of columns in formulas

jthi — Thu, 07 Oct 2021 19:04:08 GMT

Again JMP Discovery Summit Meet the Experts delivers! Hopped to Scripting for Automation & Cust breakout room and @EvanMcCorkle had way cleaner solution than my looping insert into and eval parse; substituting the List around listOfCols with ColSum:

listOfCols = {:height, :age, :sex}; //won't work here
f =  Substitute(listOfCols, Expr(list), Expr(ColSum));
Show(f); //f = ColSum(:height, :age, :sex);

Not sure if the way Col Sum() functions work with list of columns, but this solution is clean and simple.

topic Re: Using list of columns in formulas in Discussions

Using list of columns in formulas

Re: Using list of columns in formulas

Re: Using list of columns in formulas