Search code examples
sastransposeproc

How to transpose data with many columns in SAS

Hi I new in SAS and I have a problem. I have the data which contains 337 rows and 64 columns. It looks like this: enter image description here I need to make like this: enter image description here

I've tried to use proc transpose, but I felt. Please help.

Solution

  • You will need to TRANSPOSE BY DATE and then SORT by country date, I presume country is from the label of the original variables.

    Example: 3 dates and 5 variables, easily changed for larger data.

    data total_returns(label='Example data');
  do date = '01jan2020'd to '03jan2020'd;
    array RI RI_1-RI_5;
    do over ri;
      demo_value + 1;
      RI = demo_value;
    end;
    output;
  end;
  label
    RI_1 = 'MSCI Country Biff blah blah blah'
    RI_2 = 'MSCI Country Bam blah blah blah'
    RI_3 = 'MSCI Country Boom blah blah blah'
    RI_4 = 'MSCI Country Zwok blah blah blah'
    RI_5 = 'MSCI Country Pow blah blah blah'
  ;
  format date yymmdd10.;
  drop demo_value;
run;

proc transpose 
  data=total_returns 
  out=stage1 ( 
    drop=_name_ 
    rename= ( _label_=Country  
              col1 = RI
            ) 
  )
;
  by date;
  var RI_1-RI_5;
  label country = ' ';
run;

proc sort data=stage1 out=want;
  by country date;
run;

proc print label data=total_returns;
  title "Original, across/array layout";
proc print data=want;
  title "Transposed and Sorted, categorical/vector layout";
run;

    Output:
    enter image description here