1 data a;
 2     input name $ a b ;
 3     cards;
 4 x         2         4
 5 x         3         2
 6 x         4         4
 7 y         5         1
 8 y         6         2
 9 y         7         1
10 ;
11 run;
12 %macro transpose;
13 proc sql noprint ;
14     select count (distinct name) into:name_n  from a;
15     select distinct name into: name_ separated by"|" from a;
16 quit;
17 %do i = 1 %to &name_n;
18     %let _name = %scan("&name_",&i,"|");
19 proc sql noprint;   
20     select count(a) into: na from a where name="&_name" ;
21     select name into:name_x from a where name="&_name";
22     select a into: aa  separated by "|" from a where name="&_name";
23     select count(b) into: nb from a where name="&_name";
24     select name into:name_x from a where name="&_name";
25     select b into: bb  separated by "|" from a where name="&_name";
26 quit;
27 data tmp;
28     set a(drop=name);
29 run;
30 proc sql noprint;
31     select name into:varlist separated by "|" from dictionary.columns
32     where libname="WORK" and memname="TMP";
33 quit;
34 data _null_ ;
35     nvar=count("&varlist","|")+1;
36     call symput("nvar",nvar);
37 run;
38  %do j=1 %to &nvar;
39   %let _var=%scan(%bquote(&varlist),&j,"|");
40 data b&i.&j.(keep=name _name_ col: );
41     name="&_name";
42     _name_="&_var";
43     array _a[&na]  ;
44     array _b[&nb]  ;
45     do i = 1 to &na;
46         _a[i]=scan("&aa",i,"|");
47     end;
48     do j = 1 to &nb;
49         _b[j]=scan("&bb",j,"|");
50     end;
51    %do p= 1 %to &&n&_var;
52        rename _&_var.&p=col&p;
53    %end;
54     proc sort nodupkey;by name col:;
55 run;
56  %end;
57 %end;
58 data wanted ;
59     set b:;
60 run;
61 %mend transpose;
62 %transpose