turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- how to pass a column as parameter in a formula

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Aug 21, 2017 4:53 AM
(2775 views)

Hi,

I am trying to create a simple formula on columns that are stored as variables in a list.

I do not understand why JSL refuses to perform the formula.

If I change in the formula the parameter by hte string it stores, it works perfectly.

Any help ??

Here is the code I use:

```
Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
MyList = {"age", "height"};
ParamPlot = {"age2", "height2"};
for(j=1, j <= N items(MyList), j++,
dt << New Column( ParamPlot[j], Formula( 2* ParamPlot[j]) );
);
```

I tried things like Eval, Eval expr,...but noway :(

4 ACCEPTED SOLUTIONS

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Aug 21, 2017 6:27 AM
(4808 views)

The problem is that the argument to Formula() is treated as a literal expression. It does not evaluate ParamPlot[j] and substitute the result to make the formula you want. It seems like you should be able to use expressions but I got no where with that approach so here is a solution based on character strings instead:

```
Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
MyList = {"age", "height"};
ParamPlot = {"age2", "height2"};
For( j = 1, j <= N Items( MyList ), j++,
Eval(
Parse(
Substitute("dt << New Column( \!"nnn\!", Formula( 2 * :ppp ) )",
"nnn",
ParamPlot[j],
"ppp",
MyList[j]
)
);
);
);
```

I really don't like using character strings but sometimes, you gotta do what you gotta do.

Learn it once, use it forever!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Aug 21, 2017 7:34 AM
(4798 views)

Maybe this is slightly better (in the eyes of some):

```
Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
MyList = {"age", "height"};
ParamPlot = {"age2", "height2"};
For( j = 1, j <= N Items( MyList ), j++,
CMD = Expr(dt << New Column( NameTBD, Formula( 2 * colTBD) ));
SubstituteInto(CMD, Expr(NameTBD), ParamPlot[j], Expr(ColTBD), Parse(":"||MyList[j]));
CMD;
);
```

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Aug 21, 2017 8:54 AM
(4782 views)

it looks like you're trying to make a formula that multiplies its self by 2. this won't work and should return an empty value.

Also, "j" is a function for making matricies (note that it turns blue in the JSL editor) so I usually avoid using "j" as a variable just in case it might get interpeted in an unexpected way.

Working with column names and references can be pretty frustrating. Take a look at this strategy.

Inside the loop, first the column is created, and as its created, its given a handle "newcol" to use as a reference. Next there is the big eval(substitute)) clause. Inside this argument, the "col<<formula()" is defined as an expression (expressons aren't evaluated) with a place holder for the column name. Then that place holder is substuted for the column name. Still nothing is evaluated. After the substitution, the whole thing gets evaluated, because it wrapped in Eval.

```
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
MyList = {"age", "height"};
ParamPlot = {"age2", "height2"};
For( i = 1, i <= N Items( MyList ), i++,
newcol=dt << New Column( ParamPlot[i] );
Eval(
Substitute(
Expr(
newcol << set formula( a * 2 ) ) ,
Expr( a ), As Name( mylist[i] )
);
));
```

JMP Systems Engineer, Pharm and BioPharm Sciences

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Aug 22, 2017 7:56 AM
(4694 views)

I couldn't resist one last take. Short and sweet and it works. Use **evalinsert** to evaluate variables, **:name()** to refer to columns, **"\[ ]\"** to allow double quotes inside a string, and finally use **eval(parse())** to run the resulting string.

```
Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
MyList = {"age", "height"};
ParamPlot = {"age2", "height2"};
For( j = 1, j <= N Items( MyList ), j++,
new_col_expr = evalinsert("\[dt << New Column("^paramplot[j]^", Formula(2 * :name("^mylist[j]^")))]\");
eval(parse(new_col_expr));
);
```

7 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Aug 21, 2017 6:27 AM
(4809 views)

The problem is that the argument to Formula() is treated as a literal expression. It does not evaluate ParamPlot[j] and substitute the result to make the formula you want. It seems like you should be able to use expressions but I got no where with that approach so here is a solution based on character strings instead:

```
Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
MyList = {"age", "height"};
ParamPlot = {"age2", "height2"};
For( j = 1, j <= N Items( MyList ), j++,
Eval(
Parse(
Substitute("dt << New Column( \!"nnn\!", Formula( 2 * :ppp ) )",
"nnn",
ParamPlot[j],
"ppp",
MyList[j]
)
);
);
);
```

I really don't like using character strings but sometimes, you gotta do what you gotta do.

Learn it once, use it forever!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Aug 21, 2017 7:34 AM
(4799 views)

Maybe this is slightly better (in the eyes of some):

```
Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
MyList = {"age", "height"};
ParamPlot = {"age2", "height2"};
For( j = 1, j <= N Items( MyList ), j++,
CMD = Expr(dt << New Column( NameTBD, Formula( 2 * colTBD) ));
SubstituteInto(CMD, Expr(NameTBD), ParamPlot[j], Expr(ColTBD), Parse(":"||MyList[j]));
CMD;
);
```

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Aug 21, 2017 8:54 AM
(4783 views)

it looks like you're trying to make a formula that multiplies its self by 2. this won't work and should return an empty value.

Also, "j" is a function for making matricies (note that it turns blue in the JSL editor) so I usually avoid using "j" as a variable just in case it might get interpeted in an unexpected way.

Working with column names and references can be pretty frustrating. Take a look at this strategy.

Inside the loop, first the column is created, and as its created, its given a handle "newcol" to use as a reference. Next there is the big eval(substitute)) clause. Inside this argument, the "col<<formula()" is defined as an expression (expressons aren't evaluated) with a place holder for the column name. Then that place holder is substuted for the column name. Still nothing is evaluated. After the substitution, the whole thing gets evaluated, because it wrapped in Eval.

```
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
MyList = {"age", "height"};
ParamPlot = {"age2", "height2"};
For( i = 1, i <= N Items( MyList ), i++,
newcol=dt << New Column( ParamPlot[i] );
Eval(
Substitute(
Expr(
newcol << set formula( a * 2 ) ) ,
Expr( a ), As Name( mylist[i] )
);
));
```

JMP Systems Engineer, Pharm and BioPharm Sciences

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Aug 21, 2017 10:38 AM
(2730 views)

As Byron said you are trying to create a new column that is 2 times itself. I think this is what you want. AS COLUMN did the trick.

```
Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
MyList = {"age", "height"};
ParamPlot = {"age2", "height2"};
For( j = 1, j <= N Items( MyList ), j++,
dt << New Column( ParamPlot[j], Formula( 2 * as column(dt, MyList[j]) ) )
);
```

This works great as long as you don't add rows. Then you'll need the evals, exprs etc.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Aug 22, 2017 6:17 AM
(2693 views)

First of all, thank you all for your answers...

Here is my view on all the solutions proposed:

1/ The solution proposed by Mark: Works perfectly (though difficult to read when not used to Eval, parse, Substitue,...).

2/ The solution proposed by Jan: Indeed, better in the eyes (also uses eval, substituteinto,...). By the way it works perfectly.

3/ The Solution of Byron: Also uses Eval, substitute, expr...Works perfectly

4/ The solution of pmroz is by far the simplest (to the eyes), but the formula we get is still referenced to MyList and not to the columns they should represent...Meaning that if I change a value in the column "age" , the column "age2" will not be updated.

Thank you all for the suggestions :)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Aug 22, 2017 7:56 AM
(4695 views)

I couldn't resist one last take. Short and sweet and it works. Use **evalinsert** to evaluate variables, **:name()** to refer to columns, **"\[ ]\"** to allow double quotes inside a string, and finally use **eval(parse())** to run the resulting string.

```
Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
MyList = {"age", "height"};
ParamPlot = {"age2", "height2"};
For( j = 1, j <= N Items( MyList ), j++,
new_col_expr = evalinsert("\[dt << New Column("^paramplot[j]^", Formula(2 * :name("^mylist[j]^")))]\");
eval(parse(new_col_expr));
);
```

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Aug 23, 2017 12:42 AM
(2660 views)

Short and efficient :)

Thanks a lot :)