The SAS Output Delivery System and reporting techniques

Combine Several Variables into One Variable

Accepted Solution Solved
Reply
Regular Contributor
Posts: 228
Accepted Solution

Combine Several Variables into One Variable

The data presented here is completely made up, but the principle is the same.  The data that was given to me has several variables pertaining to one question because participants were allowed to check more than one box.  The data came where if they checked the first box, then the first variable was 1, 0 otherwise.  The same for the second choice, and so on.

I want to format the variables, combine all the variables into a single variable, and only show data that was checked.  Below is a simple example of what I want to do.

proc format;

      value color

            1='Red'

            2='Orange'

            3='Yellow'

            4='Green'

            5='Blue'

            6='Purple';

run;

data results;

      input choice1 choice2 choice3 choice4 choice5 choice6;

      if choice2=1 then choice2=2;

      if choice3=1 then choice3=3;

      if choice4=1 then choice4=4;

      if choice5=1 then choice5=5;

      if choice6=1 then choice6=6;

   choice=catx(', ',put(choice1,color.),put(choice2,color.),put(choice3,color.),put(choice4,color.),put(choice5,color.),put(choice6,color.));

      datalines;

0     1     1     0     1     0

1     0     1     1     0     0

0     0     0     1     1     0

1     0     1     0     0     1

;

run;

The results should come out as:

Orange, Yellow, Blue

Red, Yellow, Green

Green, Blue

Red, Yellow, Purple

I can almost get to this point, except I have a bunch of "0" in the desired variable.  I have tried to play with TRANWRD(), but there are various situations such that there is no simple way to code it (as far as I can tell).  I have even tried using a macro where I would only concatenate if one of the concatenating variables was not 0.  But that didn't pan out as planned.  I may need to loop through each participant as well as each variable.

Any thought?  Any assistance is greatly appreciated!


Accepted Solutions
Solution
‎04-15-2013 02:58 PM
Respected Advisor
Posts: 3,156

Re: Combine Several Variables into One Variable

A small tweak should do:

data results;

  input choice1 choice2 choice3 choice4 choice5 choice6;

  if choice2=1 then choice2=2;

  if choice3=1 then choice3=3;

  if choice4=1 then choice4=4;

  if choice5=1 then choice5=5;

  if choice6=1 then choice6=6;

length choice $50.;

array t choice1-choice6;

do over t;

if t ne 0 then choice=catx(', ', choice, put(t, color.)); end;

/* choice=catx(', ',put(choice1,color.),put(choice2,color.),put(choice3,color.),put(choice4,color.),put(choice5,color.),put(choice6,color.));*/

  datalines;

0 1 1 0 1 0

1 0 1 1 0 0

0 0 0 1 1 0

1 0 1 0 0 1

;

run;

Haikuo

View solution in original post


All Replies
Solution
‎04-15-2013 02:58 PM
Respected Advisor
Posts: 3,156

Re: Combine Several Variables into One Variable

A small tweak should do:

data results;

  input choice1 choice2 choice3 choice4 choice5 choice6;

  if choice2=1 then choice2=2;

  if choice3=1 then choice3=3;

  if choice4=1 then choice4=4;

  if choice5=1 then choice5=5;

  if choice6=1 then choice6=6;

length choice $50.;

array t choice1-choice6;

do over t;

if t ne 0 then choice=catx(', ', choice, put(t, color.)); end;

/* choice=catx(', ',put(choice1,color.),put(choice2,color.),put(choice3,color.),put(choice4,color.),put(choice5,color.),put(choice6,color.));*/

  datalines;

0 1 1 0 1 0

1 0 1 1 0 0

0 0 0 1 1 0

1 0 1 0 0 1

;

run;

Haikuo

Regular Contributor
Posts: 228

Re: Combine Several Variables into One Variable

This did the trick!  It's always a small thing.  I did think I might need to use an array, but I rarely use them, so I wasn't quite sure how to begin.  Thank you so much!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 197 views
  • 0 likes
  • 2 in conversation