Split single column into multiple columns (use values as variables)

[nop]

Hi all,

I have been using this community to learn more about the codes.  May I ask for your expertise?  Here's the data I have and the output I want.  I tried to search some old posts but can't figure it out.  Many thanks for your help!

data have;
Zoo = 'A'; Animal = 'Big cat; Tiny strong dog; White pony'; output;
Zoo = 'B'; Animal = 'Big cat; Guinea pig'; output;
Zoo = 'C'; Animal = 'White pony; Small rabbit'; output;
Zoo = 'D'; Animal = 'Tiny strong dog; White pony'; output;
run;

Output I want
Zoo    Big cat     Tiny strong dog     White pony    Guinea Pig    Small Rabbit
A         1              1                            1                       0                  0
B         1              0                            0                       1                  0
C         0              0                            1                       0                  1
D         0              1                            1                       0                  0

[PaigeMiller]

Please allow me to suggest a far better data structure for the output — long, instead of wide. This will make your programming now and in the future much easier.

data want;
    set have;
    do i=1 to countw(animal,';');
        this_animal=strip(scan(animal,i,';'));
        output;
    end;
    drop animal i;
run;

[ballardw · Accepted solution]

By default SAS will not create variables that include spaces or any character other than letters, digits or underscores and cannot start with a digit.

Why exactly do you think you want a data set in that format? What can you do with it?

A typical more useful data set would be to have something that looks like

Zoo Animal

A    "Big cat"

A    "Tiny strong dog"

A    "White Pony"

Then if you need to count things a report will make columns using the animal values. One possibility:

data have;
Zoo = 'A'; Animal = 'Big cat; Tiny strong dog; White pony'; output;
Zoo = 'B'; Animal = 'Big cat; Guinea pig'; output;
Zoo = 'C'; Animal = 'White pony; Small rabbit'; output;
Zoo = 'D'; Animal = 'Tiny strong dog; White pony'; output;
run;

data want;
   set have (rename=(animal=oldanimal));
   length animal $ 15;
   do i=1 to countw(oldAnimal,';');
      Animal = strip(scan(oldanimal,i,';'));
      output;
   end;
   drop oldanimal i;
run;

options missing='0';
proc report data=want;
   column zoo animal;
   define zoo/ group;
   define animal/across order=data;
run;
options missing='.';

One advantage of this approach is that if you only want to look at some animals you can select by using the Animal variable in a Where statement or data set option such as: Where Animal in ("Big cat" "White Pony"); With your data set you would have to select multiple variables AND remove other variables which with those "names" could get cumbersome pretty quickly. Consider, just how many animals a real zoo might have...

Another is if you have similar values that you later want to group together, such as "Big cat" and "Small cat" you could create a format that would display the value "Cat" and would group both big and small cat for reporting, graphing or modeling. With yours you would need to take another pass through the data with each such change and add more variables but you could use different formats with the same data to do many things.

[nop]

PaigeMiller and Ballardw, thanks a million for the suggestions!! I have tried both solutions.  Ballardw, you are right. I should think about and work on a better grouping, so that the same dataset can be used for different analysis.  That's so much to learn and really thanks alot for your help!  Have a good day!