Subscribe Bookmark RSS Feed

Combine Multiple Single Punch/Binary Variables into One Variable

mcstagger

Community Trekker

Joined:

Oct 6, 2014

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?

8 REPLIES
pmroz

Super User

Joined:

Jun 23, 2011

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

mcstagger

Community Trekker

Joined:

Oct 6, 2014

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

Joined:

Jun 23, 2011

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

Community Trekker

Joined:

Oct 6, 2014

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

pmroz

Super User

Joined:

Jun 23, 2011

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

Community Trekker

Joined:

Oct 6, 2014

Thank you for clarifying the logic in your code block.

mcstagger

Community Trekker

Joined:

Oct 6, 2014

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

Joined:

Jun 23, 2011

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;