cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
mcstagger
Level III

Combine Multiple Single Punch/Binary Variables into One Variable

I have a set of data where we asked in which industry a respondent works. They are given a small list of options and can only select one. When the data comes back from processing, that single question is blown out to one variable per option rather than a single variable with multiple coded labels. What I am trying to figure out is how to use a JMP formula to read each of the variables within a case and apply the appropriate coded label in a new variable based on where the 1 or "Yes" is located.

I could easily do this in Excel and import the data back into JMP, but I would really like to learn how to do this within JMP.

Does anyone have a good example of collapsing multiple binary variables into one using formulas?

1 ACCEPTED SOLUTION

Accepted Solutions
mcstagger
Level III

Re: Combine Multiple Single Punch/Binary Variables into One Variable

So what I ended up doing is creating a nested IF shown below. I then went in and added Value Labels for each of the points and I get essentially what I am looking for but I am curious if there is a better way to get where I have ended up. I have attached the sample data which includes my solution.

Couple notes on what I wrote, the final if statement is completely unnecessary and having Error outside of quote would, ironically, cause an error.

7630_nested if.png

View solution in original post

8 REPLIES 8
pmroz
Super User

Re: Combine Multiple Single Punch/Binary Variables into One Variable

Can you provide a sample of your data?  JMP has very powerful commands like STACK and SPLIT which may be helpful.

mcstagger
Level III

Re: Combine Multiple Single Punch/Binary Variables into One Variable

I attached a sample to my original post.

Respondents are asked a question like...

In which of the following industries do you work? (Select one response)

Industry 1

Industry 2

Industry 3

Industry 4

Industry 5

Industry 6

Industry 7

Industry 8

Since the respondent can only select one response, I would expect this question to be output as a single variable with each of the possible industries as a coded option for that variable. What I get is a variable for each option.

Does that, with the sample data, make what I am looking for more clear?

pmroz
Super User

Re: Combine Multiple Single Punch/Binary Variables into One Variable

Here's one way:

1. Create a new column, for example Response, and make it Character type

2. Click on Column Properties > Formula

3. Click on Edit Formula

3. Double click on the formula rectangle and enter the following code:

If(:worksc1, "Worksc1", :worksc2, "Worksc2", :worksc3, "Worksc3", :worksc4, "Worksc4", :worksc5, "Worksc5", :worksc6, "Worksc6", :worksc7, "Worksc7", :worksc8, "Worksc8")

Basically it's a giant IF statement that looks for Yes values (which are coded to 1s).  The Response column will now show which column contained Yes.

mcstagger
Level III

Re: Combine Multiple Single Punch/Binary Variables into One Variable

Thank you PMroz, it looks like I ended up where you did as you can see in my post below.

pmroz
Super User

Re: Combine Multiple Single Punch/Binary Variables into One Variable

The : prefix tells JMP that this is a column.  You can only use :column name if there are no "funny" characters in the column name, like /&*^%$# etc.

JMP will evaluate any positive integer to TRUE, so I just used the column value instead of comparing it to 1.

These two sets of JSL statements are equivalent:

abc = 1;

if (abc, print("abc is True"));

xyz = 1;

if (xyz == 1, print("xyz is True"));

The log shows this output:

"abc is True"

"xyz is True"

mcstagger
Level III

Re: Combine Multiple Single Punch/Binary Variables into One Variable

Thank you for clarifying the logic in your code block.

mcstagger
Level III

Re: Combine Multiple Single Punch/Binary Variables into One Variable

So what I ended up doing is creating a nested IF shown below. I then went in and added Value Labels for each of the points and I get essentially what I am looking for but I am curious if there is a better way to get where I have ended up. I have attached the sample data which includes my solution.

Couple notes on what I wrote, the final if statement is completely unnecessary and having Error outside of quote would, ironically, cause an error.

7630_nested if.png

ms
Super User (Alumni) ms
Super User (Alumni)

Re: Combine Multiple Single Punch/Binary Variables into One Variable

Another way: Stack your data table (Tables menu) and delete all No rows (Right-click on a "No", pick "Select Matching Cells"  and then Delete Rows from Rows menu).

Same in JSL:

dt = Data Table( "Data Subset.jmp" ) << Stack(

  columns(

  :worksc1,

  :worksc2,

  :worksc3,

  :worksc4,

  :worksc5,

  :worksc6,

  :worksc7,

  :worksc8

  ),

  Source Label Column( "Label" ),

  Stacked Data Column( "Data" )

);

dt << select where( :Data == 0 ) << delete rows;