Home Forums Main Forums SAS Forum Transpose data by Proc Transpose and Data Step Array

  • Transpose data by Proc Transpose and Data Step Array

     Datura updated 3 years, 1 month ago 1 Member · 1 Post
  • Datura

    Member
    February 1, 2021 at 11:22 am

    In SAS, if we want to reshape data from long to wide, we have 2 options: Proc Transpose and DATA step array. Proc Transpose is easy but it is NOT flexible. Array method require more syntax, however, it is much more flexible than Proc Transpose. Therefore, sometimes we need to use Array to reshape data when Proc Transpose fails to work. Below code illustrate the 2 methods

    1. Proc Transpose: it is easy to use with concise syntax.

     Data AAA;
    Input Sales_ID $ Region $ Turnover ;
    cards;
    X A 7
    Y C 8
    Y D 5
    Z B 6
    Z M 333
    ;
    run;
    Proc sort data=AAA; By sales_ID; run;

    Proc transpose data=AAA out=BBB (drop=_Name_) prefix=Region_ ;
    ID region;
    Var turnover;
    By Sales_ID;
    run;

    Below is the output:
    Obs Sales_ID Region_A Region_C Region_D Region_B Region_M
    1 X 7 . . . .
    2 Y . 8 5 . .
    3 Z . . . 6 333

    2. Array method: flexible and customizable. Please note that we must use RETAIN statement to fulfill our task.

     proc sort data=AAA; by Sales_ID ; run;

    data BBB;
    set AAA;
    by Sales_ID;
    if Region='A' then n=1;
    else if Region='B' then n=2;
    else if Region='C' then n=3;
    else if Region='D' then n=4;
    else if Region='M' then n=5;
    else n=999;

    array region_t(*) region_A region_B region_C region_D region_M;
    retain region_A region_B region_C region_D region_M;
    if first.Sales_ID then do i=1 to dim(region_t);
    region_t(I)=. ;
    end;

    region_t(n)=Turnover;
    if last.Sales_ID;
    drop n i Turnover region;
    run;

    It produces the identical results as the Proc Transpose.

    • This discussion was modified 3 years, 1 month ago by  Datura.

Log in to reply.

Original Post
0 of 0 posts June 2018
Now