topic Re: transpose in SAS Programming

transpose

Kyojik — Tue, 30 Jan 2018 15:12:10 GMT

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.

Re: transpose

RW9 — Tue, 30 Jan 2018 15:35:19 GMT

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.

Re: transpose

Reeza — Tue, 30 Jan 2018 15:57:49 GMT

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.

Re: transpose

Rick_SAS — Tue, 30 Jan 2018 18:56:32 GMT

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;