SAS行列轉(zhuǎn)換一定要用proc transpose？No No No，介紹其他2種替代方案同樣實(shí)現(xiàn)！

行列轉(zhuǎn)換簡單示例

Before轉(zhuǎn)換

After轉(zhuǎn)換

>首選方案

proc transpose一發(fā)入魂？code示例如下

data test;
TRTN=1; AGEGR1N=1; AVAL=11; output;
TRTN=1; AGEGR1N=2; AVAL=12; output;
TRTN=1; AGEGR1N=3; AVAL=13; output;
TRTN=2; AGEGR1N=1; AVAL=21; output;
TRTN=2; AGEGR1N=2; AVAL=22; output;
TRTN=2; AGEGR1N=3; AVAL=23; output;
run;
proc transpose data=test out=test_tran0 prefix=AGEGR_;
  by TRTN;
  id AGEGR1N;
  var AVAL;
run;

>替代方案1

利用data過程步的retain+if first／if last組合

data test_tran1;
  set test;
  by TRTN AGEGR1N;
  retain AGEGR_1 AGEGR_2 AGEGR_3;
  if first.TRTN then call missing(AGEGR_1,AGEGR_2,AGEGR_3);
  if AGEGR1N=1 then AGEGR_1=AVAL;
  if AGEGR1N=2 then AGEGR_2=AVAL;
  if AGEGR1N=3 then AGEGR_3=AVAL;
  if last.TRTN then output;
  keep TRTN AGEGR_1 AGEGR_2 AGEGR_3;
run;

>替代方案2

討厭data過程步，特別偏愛sql的小伙伴們可以看看下面示例code，估計很少有人用，我反正是沒見過其他sp這個寫過，嘿嘿

proc sql noprint;
  create table test_tran2 as
  select distinct TRTN
            , max(case when AGEGR1N=1 then AVAL else . end) as AGEGR_1
            , max(case when AGEGR1N=2 then AVAL else . end) as AGEGR_2
            , max(case when AGEGR1N=3 then AVAL else . end) as AGEGR_3
  from test
  group by TRTN;
quit;

>兩種方案的執(zhí)行結(jié)果

results.png

結(jié)論

碼code是個辛苦的體力活，偶爾換個解法，換個思維，枯燥也許會變的有趣，工作也許更有動力。
以上介紹僅供參考，具體問題具體對待，如果有更有趣的解法歡迎留言探討，共同進(jìn)步。