Multiple WHERE IN that has to stay separated

Question

I'm a SQL/SAS beginner. I wanna make an animals color report, which specifies quantity for each class black/brown, white/grey, gold/silver. I think it's not efficient to write the same code every time but i'm lost how to merge it into one:

select sum(cats), sum(dogs), sum(parrots)
from animals
where animals_color in (black, brown)
_______

select sum(cats), sum(dogs), sum(parrots)
from animals
where animals_color in (white, grey)
_______

select sum(cats), sum(dogs), sum(parrots)
from animals
where animals_color in (gold, silver)

I would like something like this but it doesn't return desired table:

select sum(cats), sum(dogs), sum(parrots)
from animals
where 
(animals_color in (black, brown))
or
(animals_color in (white, grey))
or
(animals_color in (gold, silver))

I would very appreciate any tips or help! SAS's Data Step tips or alternative solution are welcome as well.

Answer 1

A SAS-y way to do this would be to create a custom format for the animal color type and then use a SAS procedure to create your desired output. My example uses PROC MEANS, but others like TABULATE or REPORT could easily be used.

data have;
format animals_color $8. cats dogs parrots best.;

do i=1 to 10;
    do animals_color='black', 'brown','white', 'grey','gold','silver';
        cats = floor(ranuni(1)*10);
        dogs = floor(ranuni(1)*10);
        parrots= floor(ranuni(1)*10);
        output;
    end;
end;
drop i;
run;

proc format ;
value  $animal_ctype 'black'='black/brown'
                   'brown'='black/brown'
                   'white'='white/grey'
                   'grey'='white/grey'
                   'gold'='gold/silver'
                   'silver'='gold/silver'
                   other='unknown';
run;

proc means data=have sum;
var cats dogs parrots;
format animals_color $animal_ctype.;
class animals_color;
run;