Transform data lines into variables

[Fcot]

Hi all,

I am wondering how could I write the code as to transform the way the data is presented. Currently, I have the following:

Firm 1         0.001

Firm 1         0.023

Firm 1         0.002

....                 ....

Firm 4058   0.000

Firm 4058   0.001

Firm 4058   0.000

Firm 4058   0.001

I would like to transform the presentation such that the Firm 1 to 4058 are unique variables (columns) under which I have their returns:

Firm 1 ....   Firm 4058

0.001           0.000

0.023           0.001

0.002           0.000

                    0.001

I am a beginner in SAS and couldn't find examples on the internet. Hope my issue is explained clearly.

Many thanks,

[Reeza]

Here are two fully worked examples/tutorials:

https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-long-to-wide-using-proc-transpose/

https://stats.idre.ucla.edu/sas/modules/reshaping-data-long-to-wide-using-the-data-step/

[Tom · Accepted solution]

You probably need another variable to make it easy.

So add a ROW counter and then sort the data.

data fix ;
  set have ;
  by firm ;
  row+1;
  if first.firm then row=1;
run;

proc sort ;
  by row firm ;
run;

And now you can use PROC TRANSPOSE to flip it around.

proc transpose data=fix out=want ;
  by row ;
  id firm ;
  var value ;
run;

                                  Firm_
Obs    row    _NAME_    Firm_1     4058

 1      1     value      0.001     .000
 2      2     value      0.023     .001
 3      3     value      0.002     .000
 4      4     value       .        .001