Creating dummy variables from variables with multiple values separated by commas

[aespinarey]

Hello all,

I am having issues creating dummy variables (CC_Inf1) from a variable (CC_inforcvd2) with multiple values separated by commas. This variable comes from a survey question that asked participants to select all choices that applied to them. There were 13 choices.

The code I am using is:

Data PrePostZ;
if find(CC_inforcvd2, '1 ','1,') then CC_Inf1=1;
else CC_Inf1=0;
run;

I get results that are close but not exact to the actual count. For example there are 111 people who chose 1 in any sort of combination with other choices, But with this code, only 86 are counted. Please help.

Thank you,

Andrea

[PaigeMiller]

Show us a representative portion of the data. Show us the desired output.

[PeterClemmensen]

See if this logic is helpful to you. Just took the three first rows of your data..

data exampledata;
CC_inforcvd2="2,3,5,7,10,11";output;
CC_inforcvd2="1,2,3,5";output;
CC_inforcvd2="12";output;
run;

data want;
   set exampledata;
   array CC_Inf{12};
   do i = 1 to dim(CC_Inf);
      CC_Inf[i]=0;
   end;

   do i = 1 to countw(CC_inforcvd2, ',');
      idx=input(scan(CC_inforcvd2, i, ','), 8.);
      CC_Inf[idx]=1;
   end;
run;

From the look of you PDF file it seems like you write a lot of code that is not necessary. With the above logic you only have to read the data once 🙂

[ballardw · Accepted solution]

Stealing from @PeterClemmensen for example data:

data exampledata;
CC_inforcvd2="2,3,5,7,10,11";output;
CC_inforcvd2="1,2,3,5";output;
CC_inforcvd2="12";output;
run;

data want;
   set exampledata;
   array CC_Inf{12};
   do i = 1 to dim(CC_Inf);
      CC_Inf[i]= findw(CC_inforcvd2,strip(put(i,best2.)))>0;
   end;
run;

Uses findw default behavior of treating a comma as delimiter. Strip is needed to use "2" instead of " 2" as the search value.

[PeterClemmensen]

@ballardw stealing my example data and writing more efficient code.. Shame on you 😉

[ballardw]

---

@PeterClemmensen wrote:

@ballardw stealing my example data and writing more efficient code.. Shame on you 😉

---

I have had to use this approach to cleaning data for years as I deal with a variety of data sources that give me what I consider pseudo-garbage values like that.

There is one other bit of information this type of format contains that may be of interest if the Order of the output variables might indicate importance. In which case the Findw word position can be used to set a second arrays value of order for the indicator.

[aespinarey]

Thank you so much, it worked beautifully. 🙂

[Radwan]

Hello @ballardw 

I have a question 

the multiple values of mine as follows( obs1=1,2,3. obs2=3,2,4,5. obs3=4,1,2,3. so on)

so should i specify each sot of the multiple values individually in this code ?

data exampledata;
CC_inforcvd2="2,3,5,7,10,11";output;
CC_inforcvd2="1,2,3,5";output;
CC_inforcvd2="12";output;
run;

data want;
   set exampledata;
   array CC_Inf{12};
   do i = 1 to dim(CC_Inf);
      CC_Inf[i]= findw(CC_inforcvd2,strip(put(i,best2.)))>0;
   end;
run;

  that what i understood.

Also could i use this proc to categorize the multiple values based on first value in the multiple values? for instance: either obs contains 1 as first value change into zero.

thanks in advance  

[aespinarey]

No, they added that as an example of a dataset. You only need the code below that to analyze your dataset

[aespinarey]

Thank you 🙂 I greatly appreciate it.