JSL: Creating formula using a variable column name

[#1]

Hi,

I want to do an actually simple thing (at least I thought so), namely to create a new column using JSL with a formula that contains a variable column name. I already tried several combinations of the commands "column, as column, name, as name, eval, parse .." and I searched the forum, but everything I tried results in an error that the formula could not be evaluated.

An example what I want to do:
"column 1" is a column in my data table.

a = "column 1";
new column("test", formula(a*2));

The column "test" should contain the formula that multiplies "column 1" with 2.

Thanks for any help,
Dahla

[#2]

Sometimes the simplest things can be mindboggling in JSL :)

Here are a couple of approaches:

new column("test",formula( as column("column 1")*2 ));

or

a = "column 1";
new column("test",formula( a[]*2 ));

The logic of the syntax is something like this. In the first example we want to treat "column 1" as a column of numbers. In the second example, "[]" indicates that a is an array of numbers.

Hope this helps

[#3]

Thanks for the answer, but unfortunately it doesn't help.

The first approach is not what I wanted because I need to take the column name out of a variable (it can change).

The second approach does not work, because JMP doesn't insert the real column name into the formula. If you go into the dialog for changing the formula, it reads "a[Empty()] * 2" in your suggestion, and I need something like ":Column1 * 2" there.

Any other suggestions?

[#4]

I think my 2nd example should have read like this:

a = Column("column 1");
new column("test",formula( a[]*2 ));

[#5]

Thank you, that works!

[#6]

I still have an additional question:

What I finally want to do is to create a new column for each item of a list. In Stig's solution the column formula still depends on the variable "a[]" and not (as I'd prefer) on the column name "Column 1", so I'm not allowed to change "a[]" in the following script without changing the calculated column values as well. That means my following approach does not work (the length of collist and the column names can change, so I need a loop):

collist = {"column 1", "column 2"};

for (k=1, k<=length(collist), k++,
a = Column(collist);
new column(concat("test",char(k)), formula( a[]*2 ));
); 

Directly inserting a list element in the formula() statement does not work either. Does anyone have another suggestion?

Dahla

[#7]

If I use the following code:

dt = Current Data Table();
a = Column("height");
dt << New Column("test1",formula(a[]*2));
a = Column("weight");
dt << New Column("test2",formula(a[]*2));

both columns test1 and test2 are computed correctly even though the formula for both reference the same variable a. So in principle you shouldn't have a problem with the code within a loop. However, this approach doesn't work if you want the new columns to retain their behaviour in the table as computed columns - which leaves open the question still open of how to generate from code a column with the formula ":column 1 * 2" ...

[#8]

Oh, I see only now that my previous post was not fully displayed... Sorry for that.

Yes, I need the columns to retain their behaviour or at least the values. Thats why I had the idea to remove the formulas directly after calculating them. But I had the problem that I did not find a statement that does the same as "Formula... -> Clear", which means to remove the formula and retain the values as they are. Does someone know how this works?
That would not be the nicest solution, but enough for me at the moment.

[#9]

I now made the workaround to read the values out of the column into a vector and write the calculated values in a new column.

But I'm still interested in answers for the open questions.

Dahla

[#10 · Accepted solution]

After trying to come up with a working solution I finally gave up and asked JMP support how this might be done. Wendy kindly showed me the key lines and I embedded them in a for loop as a demo. The code I am including assumes that the current data table has 2 columns named abc and def which have a few rows of data of your choice. By the way there exists a new SAS Press book called Jump into JMP Scripting by Wendy Murphrey and Rosemary Lucas which is a very helpful book on scripting. It does cover Expr and Eval Expr used in the example.

dt = Current Data Table();
cols = {abc, def};
nlist = N Items(cols);
 
For( i = 1, i <= nlist, i++,
     newColExpr = Expr(
          dt << New Column( char(cols[i])||"*2", Numeric, Formula( Expr( cols[i] ) * 2 ) )
     );
     Eval( Eval Expr( newColExpr ) );
)