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 🙂
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.