Transforming data horizontal to vertical

[kky6196]

Hello, I have the following data of the form:

DATE	Australia	Austria	Denmark	Finland	France	Germany	Israel	Japan	Netherlands
9/10/2004	.	.	.	.	.	.	.	.	.
9/13/2004	-0.00111	-0.01199	-0.00347	0.00372	0.00874	0.01302	0.00454	0.00414	0.00809
9/14/2004	0.01627	0.00438	-0.00489	-0.00161	-0.00059	0.00122	0.00197	0.00753	-0.00111
9/15/2004	-0.01153	-0.01692	-0.01985	-0.0151	-0.01549	-0.01236	-0.00353	-0.0172	-0.01349
9/16/2004	0.00538	0.00506	0.00286	0.00897	-0.0013	0.00512	0.00199	-0.00103	-0.00106
9/17/2004	-0.00178	0.01367	0.00462	0.00906	0.01045	0.00823	-0.00014	-0.00819	0.01009
9/20/2004	0.00491	-0.0002	-0.00183	0.00401	-0.00672	-0.00385	0.00902	0.00191	-0.00777
9/21/2004	0.0019	0.02246	0.01675	0.0056	0.01573	0.01272	0.00026	-0.00209	0.01623
9/22/2004	0.01115	-0.00491	-0.00613	-0.00949	-0.01118	-0.01247	-0.0078	-0.00682	-0.01819
9/23/2004	0.0108	0.00498	0.00781	-0.00732	-0.00539	-0.00435	-0.02991	0.00276	-0.00587
9/24/2004	-0.00718	-0.00863	-0.00037	0.00082	0.00273	-0.00101	-0.00417	-0.01427	-0.00436
9/27/2004	0.00073	0.01044	0.00118	-0.00546	-0.00175	-0.00709	0.00147	-0.00985	-0.00324
9/28/2004	0.00752	-0.00655	0.00138	-0.00431	0.00448	0.00336	-0.00631	-0.00551	0.00365

I wish to transform as follows:

Country	Date	Returns
Australia	9/10/2004	.
Australia	9/13/2004	-0.00111
Australia	9/14/2004	0.01627
Australia	9/15/2004	-0.01153
Australia	9/16/2004	0.00538
Australia	9/17/2004	-0.00178
Australia	9/20/2004	0.00491
Australia	9/21/2004	0.0019
Australia	9/22/2004	0.01115
Australia	9/23/2004	0.0108
Australia	9/24/2004	-0.00718
Australia	9/27/2004	0.00073
Australia	9/28/2004	0.00752
Austria	9/10/2004	.
Austria	9/13/2004	-0.01199
Austria	9/14/2004	0.00438
Austria	9/15/2004	-0.01692
Austria	9/16/2004	0.00506
Austria	9/17/2004	0.01367
Austria	9/20/2004	-0.0002
Austria	9/21/2004	0.02246
Austria	9/22/2004	-0.00491
Austria	9/23/2004	0.00498
Austria	9/24/2004	-0.00863
Austria	9/27/2004	0.01044
Austria	9/28/2004	-0.00655

I would appreciate any help in writing the code for this 🙂

[Tom · Accepted solution]

If those are you only variables then a simple PROC TRANSPOSE with  BY statement will do.

First let's convert some of your listing into an actual dataset so we have something to program with.

data have;
  input DATE:mmddyy. Australia Austria Denmark Finland France Germany Israel Japan Netherlands;
  format date yymmdd10.;
cards;
9/13/2004 -0.00111 -0.01199 -0.00347 0.00372 0.00874 0.01302 0.00454 0.00414 0.00809
9/14/2004 0.01627 0.00438 -0.00489 -0.00161 -0.00059 0.00122 0.00197 0.00753 -0.00111
;

Now run the TRANSPOSE on the data.

proc transpose data=have out=want(rename=(col1=Returns)) name=Country;
  by date;
run;

Results

Obs          DATE    Country         Returns

  1    2004-09-13    Australia      -0.00111
  2    2004-09-13    Austria        -0.01199
  3    2004-09-13    Denmark        -0.00347
  4    2004-09-13    Finland         0.00372
  5    2004-09-13    France          0.00874
  6    2004-09-13    Germany         0.01302
  7    2004-09-13    Israel          0.00454
  8    2004-09-13    Japan           0.00414
  9    2004-09-13    Netherlands     0.00809
 10    2004-09-14    Australia       0.01627
 11    2004-09-14    Austria         0.00438
 12    2004-09-14    Denmark        -0.00489
 13    2004-09-14    Finland        -0.00161
 14    2004-09-14    France         -0.00059
 15    2004-09-14    Germany         0.00122
 16    2004-09-14    Israel          0.00197
 17    2004-09-14    Japan           0.00753
 18    2004-09-14    Netherlands    -0.00111

If the observation order matters then add a PROC SORT step.

If there are other variables then add a VAR statement to tell it which ones to transpose, otherwise it will transpose all of the numeric variables.