BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
kky6196
Calcite | Level 5

Hello, I have the following data of the form:

DATEAustraliaAustriaDenmarkFinlandFranceGermanyIsraelJapanNetherlands
9/10/2004.........
9/13/2004-0.00111-0.01199-0.003470.003720.008740.013020.004540.004140.00809
9/14/20040.016270.00438-0.00489-0.00161-0.000590.001220.001970.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/20040.005380.005060.002860.00897-0.00130.005120.00199-0.00103-0.00106
9/17/2004-0.001780.013670.004620.009060.010450.00823-0.00014-0.008190.01009
9/20/20040.00491-0.0002-0.001830.00401-0.00672-0.003850.009020.00191-0.00777
9/21/20040.00190.022460.016750.00560.015730.012720.00026-0.002090.01623
9/22/20040.01115-0.00491-0.00613-0.00949-0.01118-0.01247-0.0078-0.00682-0.01819
9/23/20040.01080.004980.00781-0.00732-0.00539-0.00435-0.029910.00276-0.00587
9/24/2004-0.00718-0.00863-0.000370.000820.00273-0.00101-0.00417-0.01427-0.00436
9/27/20040.000730.010440.00118-0.00546-0.00175-0.007090.00147-0.00985-0.00324
9/28/20040.00752-0.006550.00138-0.004310.004480.00336-0.00631-0.005510.00365

 

I wish to transform as follows:

CountryDateReturns
Australia9/10/2004.
Australia9/13/2004-0.00111
Australia9/14/20040.01627
Australia9/15/2004-0.01153
Australia9/16/20040.00538
Australia9/17/2004-0.00178
Australia9/20/20040.00491
Australia9/21/20040.0019
Australia9/22/20040.01115
Australia9/23/20040.0108
Australia9/24/2004-0.00718
Australia9/27/20040.00073
Australia9/28/20040.00752
Austria9/10/2004.
Austria9/13/2004-0.01199
Austria9/14/20040.00438
Austria9/15/2004-0.01692
Austria9/16/20040.00506
Austria9/17/20040.01367
Austria9/20/2004-0.0002
Austria9/21/20040.02246
Austria9/22/2004-0.00491
Austria9/23/20040.00498
Austria9/24/2004-0.00863
Austria9/27/20040.01044
Austria9/28/2004-0.00655

 

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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.

View solution in original post

1 REPLY 1
Tom
Super User Tom
Super User

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.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 294 views
  • 1 like
  • 2 in conversation