topic Re: Transform data lines into variables in SAS Data Management

Transform data lines into variables

Fcot — Thu, 09 Nov 2017 21:36:02 GMT

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,

Re: Transform data lines into variables

Reeza — Thu, 09 Nov 2017 21:43:26 GMT

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/

Re: Transform data lines into variables

Tom — Thu, 09 Nov 2017 22:09:08 GMT

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