How to write a command on Formula-Character

[#1]

I will like to know how to write a workable command using Formula-Character functionality.

For example, I have a column of character data that have data like leveled from Class 1.0 to Class 9.0.

I would like convert this information to another column that only show 1.0 to 9.0, how can I use the formula character function to write a command for this?

The other request I like to know is how can I change a column of character data to another statement in a new column.

For example, I have a character column of data showing sweet, tasty, sour,...and I would like to change it to be sweet-->Bin1, tasty-->Bin2, sour-->Bin3. How can I use the formula character to convert the sweet-->Bin1 in a new column.

Thanks,

[#2]

Hi bernie426,

To extract a piece of a your column you could use the Word() function, which takes two arguments: the first is which "word" to extract (you will enter a number here), and the second is the column with text you wish to extract from. There is a third, optional argument in which you can specify the delimiters that separate words, but in your case the default of a "space" will work. So, to use this, make a new formula column with Word(2,:Class)  (where :Class is whatever variable your original data is stored in).  For example (also in the attached dataset):

This will take the second "word" which is just whatever is after the first space encountered, thus just the numbers.

For your second question I would use the match function (below). You can write this yourself, or use the column recode utility to write it for you. To do the latter, select the original column, then go to Cols > Recode. Specify what new values you wish your variable to have, then select "Formula Column" instead of "In Place."  JMP will make you a new column using the match function.

You could use Cols > Recode in this same way for your first question, but if you have many levels using Word() will be faster than manually specifying the recoding.

I've attached an example dataset with these formulae.

I hope this helps!

julian

[#3]

Thanks a lots for the useful information Julinan.

Bernie

[#4]

You're welcome! I just read your initial post again and it occurred to me you might have been looking for a scripting solution rather than a column formula solution. If that is the case let me know and I can help you set this up through a script. You may have noticed in Col > Recode there is even an option to have JMP write the recoding as a script, so I would start there and we could adapt the code to use Word() to address your first question.

Julian

[#5]

Hi Julian,

So appreciate that you read my initial post again. Yes, it would be even better if there was having a script option since I am using this a daily based.

[#6]

Hi Bernie,

I'm glad I asked! There are a few ways you can approach this, depending on whether you would like to write a formula column, or write the actual values. Different circumstances call for one or the other, so I've included methods for both below.

To write the actual values:

Using Word() to extract just the numbers
//Make a new column Current Data Table() << New Column("ClassNumberOnly", Character); //stop table update until all changes are made, good for large tables Current Data Table() << Begin Data Update; //Apply changes to each row For Each Row(   :ClassNumberOnly = Word( 2, :Class ) ); // End "Data Update" Current Data Table() << End Data Update;

Using Match() for recoding

//Make a new column Current Data Table() << New Column("AttributeRecode", Character); //stop table update until all changes are made, good for large tables Current Data Table() << Begin Data Update; //Apply changes to each row For Each Row(   :AttributeRecode = Match( :Attribute,   "Tasty", "Bin3",   "Sweet", "Bin2",   "Sour", "Bin1",   :Attribute   ) ); // End "Data Update" Current Data Table() << End Data Update;

To write a formula column:

Using Word() to extract just the numbers
//Make the formula column Current Data Table() << New Column("ClassNumberOnly", Character,   Formula(   Word( 2, :Class )   ) );

Using Match() for recoding
//Make the formula column Current Data Table() << New Column("AttributeRecode", Character,   Formula(   Match( :Attribute,     "Tasty", "Bin3",     "Sweet", "Bin2",     "Sour", "Bin1",     :Attribute     )   ) );

Hope this helps!

julian

[#7]

Hi Julian,

I got the “Match” script or formula working perfectly, and I love it so much.

However, I cannot get the “Word” function work on either formula or script.

The case I have is an existing column that is Character of “Class 1” to Class 9”, and I will like to have a new column beside it that only show the number (1 to 9) and it is still in Character/Attribute format.

Hence, based on the Word() script you wrote for me as an example, Do we need an input information for the mother column (Class1…Class 9) and use the Word() function?

Thanks,

Bernie

Using Words() to pull out just the numbers

//Make a new column

Current Data Table() << New Column("ClassNumberOnly", Character);

//stop table update until all changes are made, good for large tables

Current Data Table() << Begin Data Update;

//Apply changes to each row

For Each Row(

:ClassNumberOnly = Word( 2, :Class )

);

// End "Data Update"

Current Data Table() << End Data Update;

[#8]

Try using the NUM function to convert the string number to an actual number.

a = "Class 9";

b = num(word(2, a));

[#9]

Hi bernie426

It sounds like you want to have the resulting column be numeric, is that right? If so, Peter's suggestion above is the right way to handle this. When you extract a piece of a string it stays a string, unless you tell JMP that you want to convert the string to be a number. Here is a completed script with the Num() function wrapped around the Word() function, and also one more change to make the new column be numeric when it is created.

Hope this works for you!

julian

//Make a new NUMERIC column

Current Data Table() << New Column("ClassNumberOnly", Numeric);

//stop table update until all changes are made, good for large tables

Current Data Table() << Begin Data Update;

//Apply changes to each row

//The function below will look for a column named "Class"

//if the data are in column with a different name

//change :Class to be whatever the column name is, but

//be sure to retain the : since that lets JMP know you are

//referencing a column.

//this function also wraps the Word() function in Num()

//which will convert the character output to be a number

//allowing the resulting column to stay numeric

For Each Row(

  :ClassNumberOnly = Num( Word( 2, :Class ) )

);

// End "Data Update"

Current Data Table() << End Data Update;

[#10]

Hi Julian,

Actually, I would like to keep it as a character rather than converting them to numeric.

What I only would like is to get rid of the "Class " from  original "Class 9.0", and have only "9.0"showing.

I used the word (2, Class),  but it did not work out.

Thanks,