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?
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)
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?
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.
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"
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.
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(
Source Label Column( "Label" ),
Stacked Data Column( "Data" )
dt << select where( :Data == 0 ) << delete rows;