SASのproc SQLでpivot変換しています。
現状、以下のプログラムで希望の出力を出せています。
「max(case when NO = # then ### end) as ##」の繰返しをすることなく、もう少しシンプルに書くことは可能でしょうか。
どなたかアドバイスをお願いいたします。
data test; input ID $ NO DAT1 $ TIM1 $ DAT2 $ TIM2 $; cards; 1 1 2020/8/4 8:30 2020/8/5 8:30 1 2 2020/8/18 8:30 2020/8/19 8:30 1 3 2020/9/1 8:30 2020/9/2 8:30 1 4 2020/9/15 8:30 2020/9/16 8:30 2 1 2020/8/4 8:34 2020/8/5 8:34 2 2 2020/8/18 8:34 2020/8/19 8:34 2 3 2020/9/1 8:34 2020/9/2 8:34 2 4 2020/9/15 8:34 2020/9/16 8:34 3 1 2020/8/4 8:46 2020/8/5 8:46 3 2 2020/8/18 8:46 2020/8/19 8:46 3 3 2020/9/1 8:46 2020/9/2 8:46 3 4 2020/9/15 8:46 2020/9/16 8:46 ; run; proc sql; create table test3 as select ID, max(case when NO = 1 then DAT1 end) as A1, max(case when NO = 2 then DAT1 end) as B1, max(case when NO = 3 then DAT1 end) as C1, max(case when NO = 4 then DAT1 end) as D1, max(case when NO = 1 then TIM1 end) as A2, max(case when NO = 2 then TIM1 end) as B2, max(case when NO = 3 then TIM1 end) as C2, max(case when NO = 4 then TIM1 end) as D2, max(case when NO = 1 then DAT2 end) as A3, max(case when NO = 2 then DAT2 end) as B3, max(case when NO = 3 then DAT2 end) as C3, max(case when NO = 4 then DAT2 end) as D3, max(case when NO = 1 then TIM2 end) as A4, max(case when NO = 2 then TIM2 end) as B4, max(case when NO = 3 then TIM2 end) as C4, max(case when NO = 4 then TIM2 end) as D4 from test group by ID; quit; proc print data = test3; run;
あなたの回答
tips
プレビュー