BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Kyojik
Obsidian | Level 7

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User

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
SAS Super FREQ

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;

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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
  • 3 replies
  • 1619 views
  • 2 likes
  • 4 in conversation