topic Creating dummy variables from variables with multiple values separated by commas in New SAS User

Creating dummy variables from variables with multiple values separated by commas

aespinarey — Wed, 20 Mar 2019 15:23:58 GMT

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

Re: Creating dummy variables from variables with multiple values separated by commas

PaigeMiller — Wed, 20 Mar 2019 15:32:19 GMT

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

Re: Creating dummy variables from variables with multiple values separated by commas

PeterClemmensen — Wed, 20 Mar 2019 15:44:21 GMT

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 🙂

Re: Creating dummy variables from variables with multiple values separated by commas

ballardw — Wed, 20 Mar 2019 15:49:28 GMT

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.

Re: Creating dummy variables from variables with multiple values separated by commas

PeterClemmensen — Wed, 20 Mar 2019 15:51:31 GMT

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

Re: Creating dummy variables from variables with multiple values separated by commas

ballardw — Wed, 20 Mar 2019 15:57:21 GMT

@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.

Re: Creating dummy variables from variables with multiple values separated by commas

aespinarey — Mon, 01 Apr 2019 16:44:04 GMT

Thank you 🙂 I greatly appreciate it.

Re: Creating dummy variables from variables with multiple values separated by commas

aespinarey — Mon, 01 Apr 2019 16:44:34 GMT

Thank you so much, it worked beautifully. 🙂

Re: Creating dummy variables from variables with multiple values separated by commas

Radwan — Wed, 03 Apr 2019 07:00:54 GMT

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

Re: Creating dummy variables from variables with multiple values separated by commas

aespinarey — Wed, 03 Apr 2019 15:35:18 GMT

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