Hello, as in the previous answer, I think that there is no general solution. However, there is a solution, if you know the categories of your transpose-id-variable (in you case "Name"). Since I don't know your data set, I will use the class dat set shipped with SAS: data Test;
set sashelp.class;
run;
proc sort data=Test; by Name Sex; run;
proc transpose data=Test out=Test_1;
id Sex;
by Name;
var Height;
run; The Variable Sex has 2 categories 'F' and 'M'. If those are not changing, you can write: proc sql;
create table Test_2 as
select Name,
max( case when Sex = 'M' then Height else . end ) as M,
max( case when Sex = 'F' then Height else . end ) as F
from Test
group by Name
order by Name;
quit; However, if those categories are changing, you will need to program a SAS-Makro to retrieve the categories into a list and do a loop: %macro transCustom(intab, outtab, idvar, byvar, varvar);
%local i numid listid;
/* Get categories and store in a list. */
proc sql noprint;
select distinct &idvar.
into :listid separated by ' '
from &intab.
order by &idvar.;
quit;
/* Number of elements in list. */
%let numid=%sysfunc(countw(&listid.));
proc sql;
create table &outtab. as
select &byvar.
%do i=1 %to &numid.;
%let curcat=%scan(&listid., &i.); /* Get i-th element from list. */
, max( case when &idvar. = "&curcat." then &varvar. else . end ) as &curcat.
%end;
from &intab.
group by &byvar.
order by &byvar.;
quit;
%mend;
%transCustom(Test, Test_3, Sex, Name, Height); I hope this helps, Daniel.
... View more