Combining Columns with Character Values

[#1]

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.

[#2]

Create a new variable (column) :  Z

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

[#3]

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?

[#4]

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];

);

[#5]

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.

[#6]

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!

[#7]

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.

[#8]

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] || ")";

);

[#9 · Accepted solution]

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 || ")"));

[#10]

Came here 11 years later to thank you for the tip; still helpful!