topic Combining Columns with Characters in Discussions

Combining Columns with Character Values

mk17 — Fri, 06 Oct 2017 15:24:24 GMT

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.

Combining Columns with Characters

mit — Wed, 08 Feb 2012 05:38:46 GMT

Create a new variable (column) : Z

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

Combining Columns with Characters

Wed, 08 Feb 2012 11:33:28 GMT

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?

Combining Columns with Characters

pmroz — Wed, 08 Feb 2012 13:52:45 GMT

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

);

Re: Combining Columns with Characters

mk17 — Wed, 08 Feb 2012 16:30:34 GMT

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.

Combining Columns with Characters

pmroz — Wed, 08 Feb 2012 16:44:35 GMT

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!

Re: Combining Columns with Characters

mk17 — Wed, 08 Feb 2012 17:23:57 GMT

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.

Combining Columns with Characters

pmroz — Wed, 08 Feb 2012 18:26:44 GMT

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

);

Combining Columns with Characters

MTOF — Thu, 09 Feb 2012 07:12:25 GMT

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

Re: Combining Columns with Characters

dumbscientist — Wed, 20 Sep 2023 13:26:00 GMT

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