DATA Step, Macro, Functions and more

transpose

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

transpose

Hello Friends, 
I am trying following transpose,

 

 

country               1920    1921 1923 1924 

A                           1           1       1       1

B                           1           2        3      1

C                           2           1         2      9

 

 

first column contains countries names and following columns contains time series exchange rate (ER) data. 

 

i want data in following shape. 

 

Country             Year           ER

A                       1920           1

A                      1921             1

A                       1923            1

A                       1924            1

B             ......................

and so on . .. .
any kind of help is appreciable. 


Accepted Solutions
Solution
‎01-31-2018 01:24 AM
Super User
Posts: 23,980

Re: transpose

PROC TRANSPOSE will do this, have you tried that approach?

 

PROC TRANSPOSE solution

https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-wide-to-long-using-proc-transpose/

 

Array solution

https://stats.idre.ucla.edu/sas/modules/reshaping-data-wide-to-long-using-a-data-step/

 


Kyojik wrote:

Hello Friends, 
I am trying following transpose,

 

 

country               1920    1921 1923 1924 

A                           1           1       1       1

B                           1           2        3      1

C                           2           1         2      9

 

 

first column contains countries names and following columns contains time series exchange rate (ER) data. 

 

i want data in following shape. 

 

Country             Year           ER

A                       1920           1

A                      1921             1

A                       1923            1

A                       1924            1

B             ......................

and so on . .. .
any kind of help is appreciable. 


 

View solution in original post


All Replies
Super User
Super User
Posts: 9,813

Re: transpose

Your column names are no 1920, 1921 etc as this is not valid in SAS (unless your using named literals).  So provide test data, in the form of a datastep which actually shows the structure of your data.

Solution
‎01-31-2018 01:24 AM
Super User
Posts: 23,980

Re: transpose

PROC TRANSPOSE will do this, have you tried that approach?

 

PROC TRANSPOSE solution

https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-wide-to-long-using-proc-transpose/

 

Array solution

https://stats.idre.ucla.edu/sas/modules/reshaping-data-wide-to-long-using-a-data-step/

 


Kyojik wrote:

Hello Friends, 
I am trying following transpose,

 

 

country               1920    1921 1923 1924 

A                           1           1       1       1

B                           1           2        3      1

C                           2           1         2      9

 

 

first column contains countries names and following columns contains time series exchange rate (ER) data. 

 

i want data in following shape. 

 

Country             Year           ER

A                       1920           1

A                      1921             1

A                       1923            1

A                       1924            1

B             ......................

and so on . .. .
any kind of help is appreciable. 


 

SAS Super FREQ
Posts: 4,269

Re: transpose

This is often called "converting from wide to long format."  You can use PROC TRANSPOSE as in this example or read this article for a DATA step or macro approach.

 

data Have;
input country $1 y1920 y1921  y1923 y1924;
Subject + 1;  /* define ID */
datalines;
A                           1           1       1       1
B                           1           2        3      1
C                           2           1         2      9
;

/** 2. Transpose the variables from wide to long format **/
proc transpose data=Have 
   out=long1(rename=(Col1=ER)) name=cYear;                    
   by Subject;                   /** for each subject **/
   var y1920 y1921  y1923 y1924; /** make a row for these variables **/
run;

/** 3. Optional: Convert to numeric year **/
data long;
set long1;
Year = input( substr(cYear, 2, 4), 4. ); 
drop cYear;
run;

proc print; run;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 123 views
  • 2 likes
  • 4 in conversation