Subscribe Bookmark RSS Feed

converting numeric data to characters

kray

Community Trekker

Joined:

Jun 23, 2011

I had to import several data type columns into jmp as numeric (coded 1-4, for example). I'd like to convert the numbers to more meaningful characters. For example in a column labeled "SITE", I'd like to make the following change:

1==DL
2==SL
3==KA
4==MP

Should this be done in the formula editor with a series of IF statements, or is there a cleaner way. If I use IF statements, then how can I delete the original numerically coded columns. Thanks in advance.
2 REPLIES
thechadd

Community Trekker

Joined:

Jun 23, 2011


style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="font-family: Arial;">I think the "match" function
would work best.  Just create a new column called SITE, and
use the following formula (replacing :Column 1 with your reference
column).


Match( :Column 1,

style="font-family: Arial;">
   
1, "DL",


   
2, "SL",


   
3, "KA",


   
4, "MP"


);

style="font-family: Arial;">


If you want to make it a script, do this:



dt

style="font-size: 10pt; font-family: "Courier New"; color: navy;">=

style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: rgb(0, 0, 221);">current
data table

style="font-size: 10pt; font-family: "Courier New"; color: black;">()

style="font-size: 10pt; font-family: "Courier New"; color: navy;">;

style="font-size: 10pt; font-family: "Courier New"; color: black;">





style="font-size: 10pt; font-family: "Courier New"; color: black;">site_col

style="font-size: 10pt; font-family: "Courier New"; color: navy;">=

style="font-size: 10pt; font-family: "Courier New"; color: black;">
dt

style="font-size: 10pt; font-family: "Courier New"; color: navy;"><<

style="font-size: 10pt; font-family: "Courier New"; color: black;">
style=""> 

style="font-size: 10pt; font-family: "Courier New"; color: rgb(0, 0, 221);">New
Column

style="font-size: 10pt; font-family: "Courier New"; color: black;">(

style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: purple;">"SITE"

style="font-size: 10pt; font-family: "Courier New"; color: navy;">,

style="font-size: 10pt; font-family: "Courier New"; color: black;">





style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="">           
Character

style="font-size: 10pt; font-family: "Courier New"; color: navy;">,

style="font-size: 10pt; font-family: "Courier New"; color: black;">





style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="">           
Nominal

style="font-size: 10pt; font-family: "Courier New"; color: navy;">,

style="font-size: 10pt; font-family: "Courier New"; color: black;">





style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="">           
Formula(

style="font-size: 10pt; font-family: "Courier New"; color: rgb(0, 0, 221);">Match

style="font-size: 10pt; font-family: "Courier New"; color: black;">(

style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: navy;">:

style="font-size: 10pt; font-family: "Courier New"; color: black;">Column
1

style="font-size: 10pt; font-family: "Courier New"; color: navy;">,

style="font-size: 10pt; font-family: "Courier New"; color: black;">





style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="">                 

style="font-size: 10pt; font-family: "Courier New"; color: teal;">1

style="font-size: 10pt; font-family: "Courier New"; color: navy;">,

style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: purple;">"DL"

style="font-size: 10pt; font-family: "Courier New"; color: navy;">,

style="font-size: 10pt; font-family: "Courier New"; color: black;">





style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="">                 

style="font-size: 10pt; font-family: "Courier New"; color: teal;">2

style="font-size: 10pt; font-family: "Courier New"; color: navy;">,

style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: purple;">"SL"

style="font-size: 10pt; font-family: "Courier New"; color: navy;">,

style="font-size: 10pt; font-family: "Courier New"; color: black;">





style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="">                 

style="font-size: 10pt; font-family: "Courier New"; color: teal;">3

style="font-size: 10pt; font-family: "Courier New"; color: navy;">,

style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: purple;">"KA"

style="font-size: 10pt; font-family: "Courier New"; color: navy;">,

style="font-size: 10pt; font-family: "Courier New"; color: black;">





style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="">                 

style="font-size: 10pt; font-family: "Courier New"; color: teal;">4

style="font-size: 10pt; font-family: "Courier New"; color: navy;">,

style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: purple;">"MP"

style="font-size: 10pt; font-family: "Courier New"; color: black;">
)





style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="">                 
)





style="font-size: 10pt; font-family: "Courier New"; color: black;">)

style="font-size: 10pt; font-family: "Courier New"; color: navy;">;

style="font-size: 10pt; font-family: "Courier New"; color: black;">





style="font-size: 10pt; font-family: "Courier New"; color: black;">site_col

style="font-size: 10pt; font-family: "Courier New"; color: navy;"><<

style="font-size: 10pt; font-family: "Courier New"; color: black;">
Delete Formula

style="font-size: 10pt; font-family: "Courier New"; color: navy;">;

style="font-size: 10pt; font-family: "Courier New"; color: black;">





style="font-size: 10pt; line-height: 115%; font-family: "Courier New"; color: black;">dt

style="font-size: 10pt; line-height: 115%; font-family: "Courier New"; color: navy;"><<

style="font-size: 10pt; line-height: 115%; font-family: "Courier New"; color: black;">
delete columns(

style="font-size: 10pt; line-height: 115%; font-family: "Courier New"; color: navy;">:

style="font-size: 10pt; line-height: 115%; font-family: "Courier New"; color: black;">Column
1)

style="font-size: 10pt; line-height: 115%; font-family: "Courier New"; color: navy;">;

gflex

Community Trekker

Joined:

Jun 23, 2011

How about column, recode (the menu option, not a formula). This can either exchange the values in place, in another column, or in a formula.