Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Combining Columns with Character Values

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Feb 7, 2012 3:24 PM
(12937 views)

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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 8

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Combining Columns with Characters

Create a new variable (column) : Z

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

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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?

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

**)**;

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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!

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

**)**;

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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