I have a dataset from a survey where one of the questions was select all that apply. When exported from Qualtrics to Excel, the variable is in this format:
Obs | Covid |
1 | 1,10,11,12,13,14 |
2 | 1,12 |
There are 16 potential response options. I am stuck how to create binary variables for each response option without hardcoding everything in excel. I ultimately want to determine how many observations selected each response option.
data have;
infile cards dsd;
input obs $ COVID : $20.;
cards;
"1", "1,10,11,12,13,14"
"2", "1,12"
;;;;
run;
data binary;
set have;
nwords = countw(covid);
array _covid(14) covid1-covid14 ;
*set all to 0;
do i=1 to 14;
_covid(i) = 0;
end;
*set to 1 for each value of the COVID variable, ie COVID1=1, COVID10=1, COVID11=1 etc;
do i=1 to nwords;
index = input(scan(covid, i), 8.);
_covid(index) = 1;
end;
drop nwords index i;
run;
@mraposa1 wrote:
I have a dataset from a survey where one of the questions was select all that apply. When exported from Qualtrics to Excel, the variable is in this format:
Obs
Covid 1
1,10,11,12,13,14 2
1,12
There are 16 potential response options. I am stuck how to create binary variables for each response option without hardcoding everything in excel. I ultimately want to determine how many observations selected each response option.
data have;
infile cards dsd;
input obs $ COVID : $20.;
cards;
"1", "1,10,11,12,13,14"
"2", "1,12"
;;;;
run;
data binary;
set have;
nwords = countw(covid);
array _covid(14) covid1-covid14 ;
*set all to 0;
do i=1 to 14;
_covid(i) = 0;
end;
*set to 1 for each value of the COVID variable, ie COVID1=1, COVID10=1, COVID11=1 etc;
do i=1 to nwords;
index = input(scan(covid, i), 8.);
_covid(index) = 1;
end;
drop nwords index i;
run;
@mraposa1 wrote:
I have a dataset from a survey where one of the questions was select all that apply. When exported from Qualtrics to Excel, the variable is in this format:
Obs
Covid 1
1,10,11,12,13,14 2
1,12
There are 16 potential response options. I am stuck how to create binary variables for each response option without hardcoding everything in excel. I ultimately want to determine how many observations selected each response option.
Thank you so much! I appreciate it!
You can also loop the other way. Loop over the codes 1 to 14 and check if they are in the list or not.
data binary;
set have;
array _covid covid1-covid14 ;
do index=1 to 14 ;
_covid[index] = 0 < indexw(covid,cats(index),', ') ;
end;
drop index;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!