topic Re: New columns in funciont of one before in SAS Programming

New columns in funciont of one before

jonatan_velarde — Thu, 28 Nov 2019 17:27:12 GMT

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

Re: New columns in funciont of one before

Reeza — Thu, 28 Nov 2019 17:30:16 GMT

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 :(.

Re: New columns in funciont of one before

jonatan_velarde — Thu, 28 Nov 2019 17:35:17 GMT

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

Re: New columns in funciont of one before

Reeza — Thu, 28 Nov 2019 18:11:36 GMT

Sorry, your By should be by Location.