had to add some string handling...
data work.have;
length
id 8
gn_name $100
race 8
;
infile cards;
input id gn_name $ 3-5 race ;
cards;
1 A 1 0
2 A 1 1
3 A 1 3
;
run;
proc sql;
insert into have (id, gn_name, race) values (1, 'Zinc SulfateHCL', 1);
insert into have (id, gn_name, race) values (2, 'Piperonyl Butoxide', 1);
insert into have (id, gn_name, race) values (3, 'Pyrilamine', 2);
select
cat("count(distinct case when trim(left(gn_name))='",trim(left(gn_name)),"' then gn_name end) as ",translate(trim(gn_name),'_',' ')) length=500 as cdef
into
:cdefs separated by ', '
from (select distinct gn_name from work.have);
%put cdefs=&cdefs;
create table want as
select
race
,&cdefs
from
work.have
group by race;
quit;
... View more
