New columns in funciont of one before

[jonatan_velarde]

Hi there:

i have this data

data have;

lenght animals$ 50;

input location animals$;

cards;

1 dog, cat, bird

2 cat

3 bird, dog

4 hamster

;

with this information, I need to keep the same column, but increase columns for each animal observed in the animal column:

LOCATION	ANIMALS	DOG	CAT	BIRD	HAMSTER
1	dog, cat, bird	1	1	1	0
2	cat	0	1	0	0
3	bird, dog	1	0	1	0
4	hamster	0	0	0	1

Where 1means YES and 0 meams NO.

Im putting this as an example, just knowing that could be more kind of animals in other more locations

Thank you in advance

[Reeza · Accepted solution]

Split them into individual rows using SCAN and then transpose it and replace the missing with 0's. It's fully dynamic for changing amount of animals and names.

location animal
1 dog
1 cat
1 bird

data long;
set have;
n=countw(animals);
do i=1 to n;
animal = scan(animals, i);
count=1;
output;
end;
run;

proc transpose data=long out=wide;
by id;
id animal;
var count;
run;

untested code :(.

[jonatan_velarde]

95
96 proc transpose data=long out=wide;
97 by id;
ERROR: Variable ID not found.
98 id animal;
99 var count;
100 run;

[Reeza]

Sorry, your By should be by Location.