BookmarkSubscribeRSS Feed
mk17

Community Trekker

Joined:

Feb 6, 2012

Combining Columns with Character Values

I have been look for a way to combine (or create) a new column that combbine the contents of 2 columns together.

 

I have a columns "Y" and "Column 1". I want the output to say "Y (Column 1)"

 

Example Y = "Kurtosis" and Column 1 = "Wheel Based Vehicle Speed" end result "Kurtosis (Wheel Based Vehicle Speed)"

 

 

I will also need to be able to delete the columns "Y" and "Column 1" afterwards but still lave my new column.

1 ACCEPTED SOLUTION

Accepted Solutions
MTOF

Community Trekker

Joined:

Jun 29, 2011

Solution

Combining Columns with Characters

You don't have to script it - just make a new column and add a formula.

Concatenation is under Character. The " (" and ")" you write yourself and the Column 1 and Column 2 are chosen from the list, so that the final formula is:

:Column 1 || " (" || :Column 2 || ")"

If you want it to be scripted, this is a shorter version of above.

dt << new column("Concatenated", character, formula(:Column 1 || " (" || :Column 2 || ")"));

8 REPLIES
mit

Community Member

Joined:

Feb 7, 2012

Combining Columns with Characters

Create a new variable (column) :  Z

Try SAS code (within a data step):  Z=compbl('"' ||Y||' ('||column1||')')

Combining Columns with Characters

You haven’t specified how you want to combine the data in the columns, but these two examples (run in JMP 9) may help you sort out the syntax you need:

Alist = {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J"};

dt = new table("DT",

       new column("Label", character, values(AList)),

       new column("Kurtosis", numeric, values([2, 4, 6, 8, 10, 2, 4, 6, 8, 10])),

       new column("Speed", values([1, 2, 3, 4, 5, 6, 7, 8, 9, 10]))

       );

dt << new column((column(dt, 1) << get name) || " (" || (column(dt, 3) << get name) || ")",

       numeric, formula(:Label || "-" || char(:Speed)));

dt << new column((column(dt, 2) << get name) || " (" || (column(dt, 3) << get name) || ")",

       numeric, formula(:Kurtosis * :Speed));

// You can't delete the first column yet, because it's used in one of the formulae above;

column(dt, 4) << delete formula;

// NOW you can delete the first column if you want to;

dt << delete columns({"Label"});

Does this help?

pmroz

Super User

Joined:

Jun 23, 2011

Combining Columns with Characters

This will do what you want.  I'm assuming that dt points to your table.

current data table(dt);

dt << new column("Y (Column 1)", character);

for (i = 1, i <= nrows(dt), i++,

    column(dt, "Y (Column 1)")[i] = :Y[i] || " " || :Column 1[i];

);

mk17

Community Trekker

Joined:

Feb 6, 2012

Re: Combining Columns with Characters

Thanks for the help guys.  I now realize that I still know very little about the JMP software. I figured this would be a very simple thing that I wasn't seeing. I have not been able to get any of these methods to work. Though, that is probably because I don't know exactly how to implement them.

To give more detail Column 1 has 22 different "phrases" and Column Y uses 2 different Words. Currently I have to manually add Column Y to the beginning of Column 1 for the entire data set. This is obviously a very time consuming process.

I have unsuccesfully tried different formulas to get what I want

I can use Insert(:Y,:Column 1) and it gives me "KurtosisWheel Based Vehicle Speed"

I can also jus type :Y" (" and will get "Kurtosis (" but I can't jsut do the same after with Column 1 to make it work.

I can't seem to find the formula that gives me both.

pmroz

Super User

Joined:

Jun 23, 2011

Combining Columns with Characters

Dear MK17,

Here's how to do it:

- Open a script window (File > New > Script).

- Copy the code below into your new script window. 

dt = data table("My Table");

current data table(dt);

dt << new column("Y (Column 1)", character);

for (i = 1, i <= nrows(dt), i++,

    column(dt, "Y (Column 1)")[i] = :Y[i] || " " || :Column 1[i];

);

- Replace "My Table" with the name of your table.

- Hit CTRL-R to run the script.

That's all!

mk17

Community Trekker

Joined:

Feb 6, 2012

Re: Combining Columns with Characters

That worked except it did not have the "()" around Column 1.

I didn't figure out a way. It might not be the most "correct" but it worked.

I created a new column (6) and then applied the formula -  " (" || :Column 1 || ")"

Then I created another new column and used the formula - Insert(:Y, :Column 6)

and viola, it is what I wanted.

Everything worked correctly and I was able to transpose my table and add it to another.

pmroz

Super User

Joined:

Jun 23, 2011

Combining Columns with Characters

Oops here's the correct code that includes the enclosing parentheses:

dt = data table("My Table");

current data table(dt);

dt << new column("Y (Column 1)", character);

for (i = 1, i <= nrows(dt), i++,

    column(dt, "Y (Column 1)")[i] = :Y[i] || " (" || :Column 1[i] || ")";

);

MTOF

Community Trekker

Joined:

Jun 29, 2011

Solution

Combining Columns with Characters

You don't have to script it - just make a new column and add a formula.

Concatenation is under Character. The " (" and ")" you write yourself and the Column 1 and Column 2 are chosen from the list, so that the final formula is:

:Column 1 || " (" || :Column 2 || ")"

If you want it to be scripted, this is a shorter version of above.

dt << new column("Concatenated", character, formula(:Column 1 || " (" || :Column 2 || ")"));