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.
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.
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.
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.
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;
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.