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
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 ) );
)
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
I think my 2nd example should have read like this:
a = Column("column 1");
new column("test",formula( a[]*2 ));
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
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" ...
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 ) );
)