transpose

[Kyojik]

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. 

[RW9]

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.

[Reeza · Accepted solution]

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. 

---

[Rick_SAS]

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;