How to analyze multiple data points in one Excel cell

[smatthy]

Hello, 

I am analyzing survey data containing a "check all that apply" question. Unfortunately the output contains all of the data in one cell.

Example question:

What fruit do you normally eat?

- Apple

- Banana

- Orange

Example datapoint:

apple,banana,orange

Datapoints containing more than one entry show up as missing in SAS, and I need to create a new variable to reflect a single entry based on a hierarchy (if q1=apple then fruit=apple; else if q1=banana then fruit=banana; etc.)

Is there a way to do this in SAS, or will I need to make changes in a copy of the Excel dataset?

Thanks

[Reeza]

Create dummy variables. A variable for apple, orange, etc and code as 0/1 for each value.

To do this, create an array with the full list of variables - assuming you know these and then use a loop with SCAN() to set them to 0/1.

Usually the survey tool has an option to export data like this (SurveyMonkey does) that says separate multiple responses or something similar to that. It’s an option to click before you export the data or the data structure.

[ballardw]

---

@smatthy wrote:

Hello, 

I am analyzing survey data containing a "check all that apply" question. Unfortunately the output contains all of the data in one cell.

Example question:

What fruit do you normally eat?

- Apple

- Banana

- Orange

Example datapoint:

apple,banana,orange

Datapoints containing more than one entry show up as missing in SAS, and I need to create a new variable to reflect a single entry based on a hierarchy (if q1=apple then fruit=apple; else if q1=banana then fruit=banana; etc.)

Is there a way to do this in SAS, or will I need to make changes in a copy of the Excel dataset?

Thanks

---

By hierarchy do you mean if apple appears anywhere in the list that would be result, if apple is not present then if banana is present that would be the result, with comparisons in order? Here would be one way:

data example;
   infile datalines truncover;
   informat string $50.;
   input string;
   array list{4} $10 _temporary_ ('apple','banana','orange','pear');
   length result $ 10;
   do i=1 to dim(list);
      if index(string,strip(list[i]))>0 then do;
         put list[i]=;
         result=list[i];
         leave;
      end;
   end;
   drop i;
datalines;
apple,banana,orange
banana,orange
banana,orange,apple
pear
apple,pear
;
run;

The number of elements in the list array, the digit in the {} must match the number of elements listed in the parentheses, the order of the values in the parentheses is the "hierarchy" order.

The LEAVE in the do loop says to exit the loop when encountered, which would be when the value of the list item is found in the string.

The STRIP function is because the value of list[I] will be padded to the defined length of the value in the temporary array (I chose 10 partially to demonstrate this). For character values it would be a good idea for the result variable and the list array size to match.

Note that if you have elements that might be part of another, such as 'apple' and 'pineapple' this won't work exactly the same.

[gdervishian]

To make sure I understand this correctly, if I set up an excel sheet with drop down choices to "check all that apply", I can't import that directly into SAS without separating the items into separate columns, right?  If I don't separate them then it has to run them as a single thing, right?  In this example, suppose I want to know whether eating apples, bananas, or oranges contributed to me being able to run faster.  It seems like I would have to separate each selection into its own column to be an independent variable, and if I don't separate them it can only run them as a combination, rather than individual contributors.  Does that make sense?

[LinusH]

Depends on how that multiple choice is represented in the data that SAS accesses.
Example above give you the option to import the data in one cell, but have SAS logic to split them up into separate columns.

[gdervishian]

Thank you.

[Patrick]

@gdervishian  Old threads should ideally be closed. This one wasn't but I'd still suggest to ask a new question instead of repurposing an 6.5 year old discussion. You can always reference the old discussion in your new question.

[gdervishian]

Thank you. I think it's still a valid thread to have because people will
search for a topic and find what's relevant. That's how I found this
thread....

[Tom]

---

@gdervishian wrote:

To make sure I understand this correctly, if I set up an excel sheet with drop down choices to "check all that apply", I can't import that directly into SAS without separating the items into separate columns, right?  If I don't separate them then it has to run them as a single thing, right?  In this example, suppose I want to know whether eating apples, bananas, or oranges contributed to me being able to run faster.  It seems like I would have to separate each selection into its own column to be an independent variable, and if I don't separate them it can only run them as a combination, rather than individual contributors.  Does that make sense?

---

It does not matter HOW you created the delimited character value.  So whether you used EXCEL to do it or whatever software the original post from 7 years ago used to create the data, if you want to treat the individual values separately you need to first separate them.

[gdervishian]

Excellent, thank you for clarifying! I really appreciate this forum.