I think it is a rather silly idea (use PROC TRANSPOSE!), but a fun challenge. I would do it like this: %macro SqlTrans;
%local i types ids id set;
proc sql noprint;
select distinct(catx(' ',type,'FLOAT')) into :types separated by ','
from test;
create table fin(id FLOAT,&types);
select distinct id into: ids separated by ' ' from test;
%do i=1 %to &sqlobs;
%let id=%scan(&ids,&i);
select cats(type,'=',sum(count)) into :set separated by ','
from test where id=&id
group by type;
insert into fin set id=&id,&set;
%end; quit;
%mend;
%SqlTrans; Note that this macro actually manages to declare all created variables local (you can do that with SQLOBS as well, if you want to). And that I managed to keep the whole shebang in SQL, no final data step. I used SUM(COUNT) and GROUP BY TYPE in case there are duplicates on TYPE within an ID.
... View more
