Coding to Separate out variables

[rebecca8]

Hi there:

I am working w/ a .csv file exported from a Qualtrics survey. One of my questions had a checklist option, and the output from Qualtrics when I run a proc freq gives a table with all possible combinations of answers. 

For instance: 12 people said option 1 and option 2, 11 people said just option 1, 110 people said option 1, 2 and 3, 40 people said options 1 and 3, 15 people said just option 3 etc. 

My goal is to get the proc freq to create a table that provides each possible answer separately, as in: how many people selected option 1, how many people selected option 2, how many people selected option 3. So that I can see each variables response separately. 

Below is what I have been told to code (these are character variables) - it doesn't generate any errors, but it produces the same proc freq table with all the possible combinations tallied. 

data sasintro.data_clean;

set allpreg;

if qx11 ^=" " then do; 

Heavy_workload="0";

Concern_regarding_career="0";

Discouraged_by_partner="0";

Discouraged_by_peers="0";

Discouraged_by_supervisor="0";

Other_please_specify="0";

end;

if index (qx11,"Heavy") then Heavy_workload="1"; 

if index (qx11,"Concern") then Concern_regarding_career="1";

if index (qx11,"partner") then Discouraged_by_partner="1";

if index (qx11,"peers") then Discouraged_by_peers="1";

if index (qx11,"supervisor") then Discouraged_by_supervisor="1";

if index (qx11,"Other") then Other_please_specify="1"; 

run;

Any ideas what my issue is? 

Thanks 🙂 

[ballardw]

---

@rebecca8 wrote:

Hi there:

I am working w/ a .csv file exported from a Qualtrics survey. One of my questions had a checklist option, and the output from Qualtrics when I run a proc freq gives a table with all possible combinations of answers. 

For instance: 12 people said option 1 and option 2, 11 people said just option 1, 110 people said option 1, 2 and 3, 40 people said options 1 and 3, 15 people said just option 3 etc. 

My goal is to get the proc freq to create a table that provides each possible answer separately, as in: how many people selected option 1, how many people selected option 2, how many people selected option 3. So that I can see each variables response separately. 

Below is what I have been told to code (these are character variables) - it doesn't generate any errors, but it produces the same proc freq table with all the possible combinations tallied. 

data sasintro.data_clean;

set allpreg;

if qx11 ^=" " then do; 

Heavy_workload="0";

Concern_regarding_career="0";

Discouraged_by_partner="0";

Discouraged_by_peers="0";

Discouraged_by_supervisor="0";

Other_please_specify="0";

end;

if index (qx11,"Heavy") then Heavy_workload="1"; 

if index (qx11,"Concern") then Concern_regarding_career="1";

if index (qx11,"partner") then Discouraged_by_partner="1";

if index (qx11,"peers") then Discouraged_by_peers="1";

if index (qx11,"supervisor") then Discouraged_by_supervisor="1";

if index (qx11,"Other") then Other_please_specify="1"; 

run;

Any ideas what my issue is? 

Thanks 🙂 

---

You would have to provide some examples of the actual starting data for your variable QX and the Proc freq code you are running. Did you use all of the new created variables or the original Qx11?

I would suggest coding things as numeric instead of character when creating 0/1 coded variables. There are some reporting shortcuts available directly in reporting procedures that often make the numeric choice preferable: Sum of the variable gives you a count of 1 values, mean gives you a percent and when comparing multiple variables on a single record you can get number easy answers to 'how many choices made' and a few other things.

Personally when dealing with such data I would name the new variable qx11_r1 to qx11_r6 so by name I know the relationship to the original question and apply the descriptive labels of "Heavy workload".

Note that similar named variable can be referenced in may procedures using a list such as:

Proc freq data=recodeddata;

    tables qx11_r: ;

run;

the colon tells SAS to use all the variables whose names start with "qx11_r";

[PaigeMiller]

Since we can't see your data, did you actually look in the data set ALLPREG or look in the data set SASINTRO.DATA_CLEAN to see what is in there? When you look at your data, does it look like the code should work?

Did you look at the LOG for warnings and/or errors? What did you find?

Is it possible that when you search for "Heavy" that the capitalization is different?

Can you show us a portion of the input data?

[rebecca8]

Hi there! Yes, I've looked at the data and it seems to me that it should work.

The capitalization is the same (I thought that too!), and the log shows no errors... it just doesn't seem to be separating out the variables when I run the proc freq. 

I'm unable to upload data because of confidentiality restrictions, but essentially for each observation, there is a possibility of 6 selections. So for each observation the input data is a list, separated by commas, of each of the selections they made (character variables). I've chosen words to search for that are unique to only one of the selections, so that should work too. 

[Reeza]

Fix it in Qualtrics?

When exporting data you can choose an option called "Split multi-value fields into columns" that will split the values into separate columns for you in the export. 

https://www.qualtrics.com/support/survey-platform/survey-module/editing-questions/question-types-gui...

---

@rebecca8 wrote:

Hi there! Yes, I've looked at the data and it seems to me that it should work.

The capitalization is the same (I thought that too!), and the log shows no errors... it just doesn't seem to be separating out the variables when I run the proc freq. 

I'm unable to upload data because of confidentiality restrictions, but essentially for each observation, there is a possibility of 6 selections. So for each observation the input data is a list, separated by commas, of each of the selections they made (character variables). I've chosen words to search for that are unique to only one of the selections, so that should work too. 

---

[PaigeMiller]

---

@rebecca8 wrote:

Hi there! Yes, I've looked at the data and it seems to me that it should work.

The capitalization is the same (I thought that too!), and the log shows no errors... it just doesn't seem to be separating out the variables when I run the proc freq. 

I'm unable to upload data because of confidentiality restrictions, but essentially for each observation, there is a possibility of 6 selections. So for each observation the input data is a list, separated by commas, of each of the selections they made (character variables). I've chosen words to search for that are unique to only one of the selections, so that should work too. 

---

Make up some data that is in the same format as the existing data, and show it to us. Or you can show us the actual data with any identifying information removed.

[Astounding · Accepted solution]

Your program (accurately) did not change the value of QX11.

When you run a PROC FREQ using the new data set, the TABLES statement has to change to reference the new variables:

tables heavy_workload concern_regarding_career discouraged_by_partner discouraged_by_peers discouraged_by_supervisor other_please_specify;

Also, I would suggest that you consider this.  Maybe it's right to set the new variables to "0" all the time, not just when QX11 has a value in it.  Either way, you will count the "1" values correctly.  But it's debatable whether you are counting the "0" values correctly.

[rebecca8]

Hi there!

This worked - thank you so much! 

And I will run it both ways with your suggestion regarding the 0 so that I can check for any missing values. 

Thank you 🙂