Subscribe Bookmark RSS Feed

applying formula to entire jmp data table

Hi,

I am new to using jmp and sas, so I apologize that this may be a simple question. I have a table with values in it, and I would like to create a new table with the same columns and rows, but with a formula applied to the entire new table. I have thousands of columns, so it is not practical to apply the formula column by column. I would really appreciate any suggestions that anyone could offer.

Thanks!
6 REPLIES
mpb

Super User

Joined:

Jun 23, 2011

I am assuming you mean to apply the exact same formula to thousands of columns where the formula involves some other group of columns in your table.

I searched the forum for: formula many columns . I didn't get an exact hit but did turn up a thread titled "Re: How to apply column properties to multiple columns?" with this link:
http://support.sas.com/forums/click.jspa?searchID=130676&messageID=27311
You want to use "Standardize Attributes" in the Columns Menu.
The "property" you want to apply is a formula. When you have selected "formula" then you will see an "edit formula" button which takes you to the formula editor.
Thanks mpb,

This sounds like it should work, but it isn't. I know you might not have any further advice, but I guess I will just throw the problem I am having out there.

What I need to do seems really simple. I have multiple columns (say x, y, and z). I want to multiply them all by 25 (e.g., 25*x, 25*y, and 25*z). When I create a formula (e.g., 25*x) and then apply it to the other columns, it applies it literally, not iterively - all the columns become 25*x (instead of 25*y, 25*z).

I tried making a "formula column" with no values in it so that the formula was empty cell*25 and applying that to the other columns, but this just puts empty cells in my table.

Thanks so much for your help - if you have any other suggestions, I would really appreciate them.

Thanks!
mpb

Super User

Joined:

Jun 23, 2011

Do you want to take your x, y, z columns and replace the values with 25*x, 25*y, 25*z, keeping the same number of columns? Or do you want 3 new columns with values 25*x, 25*y, 25*z, doubling the number of columns. Or do you want 3 new columns with column formulas 25*x, 25*y, 25*z so that when you add a row of x, y, z values you automatically get 25*x, 25*y, 25*z calculated for you (also doubling the number of columns)? Any of these can be done using a script... Let me know which it is, or if it is none of these.
Thanks again!

I would ideally like to take my columns x,y,z and replace the values with 25*x, 25*y, 25*z. Either solution would work though. If you have script suggestions, it would be really wonderful. I ws thinking that it just needs a loop that iterates through the columns, but I am really totally new to SAS/JMP. I didn't know how to call the columns. I really appreciate your help.

Thanks,
Christina
mpb

Super User

Joined:

Jun 23, 2011

Here are two scripts to try (for testing I multiplied by 10). One operates on a cell basis and the other on a matrix basis. The former should require less memory but be slower. The latter should require more memory but be faster. I'd be interested to hear if there is much difference in performance between them.

Both assume that all the columns are numeric and that you want to multiply every table cell by 10 (or 25 or whatever...).

Cell Basis:
Hi mpb,

Sorry for the delay in getting back with you - and thank you so much! I had some problems with the first script. JMP told me there were errors in the script. It seemed to be something trivial, like ( placement. I may have mis-typed something that I did not catch. I tried running the debugger, but I have not scripted in SAS or JMP before, so when I couldn't resolve the problem in a couple of minutes, I tried the matrix code. That ran beautifully, and didn't take long at all - less than a minute for both tables I needed it for (one has around 41,000 columns and the other has 60,0000). I can't tell you how much I appreciate your help.

Thanks again!