Transpose/Reshape stock data from wide to long

[nicolelai]

Hello there,

I am trying to reshape my data from wide to long format. However, I face some difficulties (that I suspect it is due to the variable names/companies ). If I need to rename the variables (900 companies), I wonder what is the best way to solve this and get my data reshaped.

The data format that I am having is shown as follow:

Date          SAPU_MK_EQUITY     NYL_MK_EQUITY   KEYA_MK_EQUITY

1/2/2000     0.013                          N/A                         0.024

1/3/2000     0.013                          N/A                         0.024   

1/4/2000     0.013                          N/A                         0.024

I would like to reshape the data to this format:

Date          Company                           EPS

1/2/2000     SAPU_MK_EQUITY          0.013

1/2/2000     NYL_MK_EQUITY             N/A

1/2/2000     KEYA_MK_EQUITY          0.024

1/3/2000     SAPU_MK_EQUITY          0.013

1/3/2000     NYL_MK_EQUITY             N/A  

1/3/2000     KEYA_MK_EQUITY          0.024

1/4/2000     SAPU_MK_EQUITY          0.013

1/4/2000     NYL_MK_EQUITY             N/A  

1/4/2000     KEYA_MK_EQUITY          0.024

Thank you.

Sincerely,

Nicole

[Haikuo]

proc transpose can do the trick:

proc transpose data=eps2 out=want(rename=(_name_=company col1=eps));

by date;

var SAPU_MK_EQUITY--NAT_MK_EQUITY;

run;

Haikuo