Multiple WHERE IN that has to stay separated

  • A+
Category:Database

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.


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; 

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: