Lapis Lazuli | Level 10

## New columns in funciont of one before

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: New columns in funciont of one before

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 :(.
3 REPLIES 3
Super User

## Re: New columns in funciont of one before

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 :(.
Lapis Lazuli | Level 10

## Re: New columns in funciont of one before

95
96 proc transpose data=long out=wide;
97 by id;