topic Re: Transpose Variables in SAS Procedures

Transpose Variables

Costasg — Wed, 18 Mar 2015 21:34:45 GMT

Hello everybody,

I need some help with transposing variables. More specifically, I use data for firms and various financial/accounting indicators. However, the database I use allows me to download the data in a specific form (see below, I tried to upload a file, but there was a message that the content of the file could not be uploaded, weird!!). For example, for interest paid, instead of having one column (variable) and get a value for each year, I get 11 different columns (for years 2005-2013).

	Company name	Country	Region	Interest paid th GBP 2013	Interest paid th GBP 2012	Interest paid th GBP 2011	Interest paid th GBP 2010	Interest paid th GBP 2009	Interest paid th GBP 2008	Interest paid th GBP 2007	Interest paid th GBP 2006	Interest paid th GBP 2005
1.	ROYAL DUTCH SHELL PLC	UNITED KINGDOM	London SE1	991.000	1.081.000	883.000	636.000	336.000	814.000	556.000	587.000	622.000
2.	BP P.L.C.	UNITED KINGDOM	London SW1Y	645.000	692.000	802.000	747.000	687.000	1.067.000	557.000	367.000	359.000
3.	VITOL HOLDING B.V.	NETHERLANDS	Rotterdam	n.a.	n.a.	n.a.	n.a.	n.a.	n.a.	202.317	170.755	41.437
4.	VOLKSWAGEN AKTIENGESELLSCHAFT	GERMANY	Wolfsburg	1.981.388	2.128.766	1.713.093	1.831.572	2.017.465	1.732.703	1.210.215	1.063.182	1.048.908
5.	TOTAL SA	FRANCE	Hauts-de-Seine	1.004.093	978.262	955.717	744.931	778.343	1.264.921	1.511.483	1.346.071	1.009.857
6.	GLENCORE PLC	UNITED KINGDOM	Jersey	1.075.000	843.000	763.000	777.000	n.a.	n.a.	n.a.	n.a.	n.a.
7.	GLENCORE INTERNATIONAL AG	SWITZERLAND	Zug	n.a.	868.821	767.091	777.663	527.324	n.a.	n.a.	n.a.	n.a.
8.	E D F TRADING LIMITED	UNITED KINGDOM	London SW1E	41.000	38.000	29.000	21.000	38.000	42.000	12.000	16.000	2.000
9.	E.ON RUHRGAS AUSTRIA GMBH	AUSTRIA	Wien	n.a.	2.176.425	n.a.	n.a.	n.a.	n.a.	n.a.	n.a.	n.a.
10.	E.ON SE	GERMANY	Düsseldorf	2.132.128	n.a.	n.a.	n.a.	1.017.628	2.913.613	1.459.312	1.484.164	1.221.557

What I want to do is have a date variable and then have a single column instead of 11 for each variable.

I would appreciate it if anyone could help.

Many thanks in advance.

Re: Transpose Variables

Reeza — Wed, 18 Mar 2015 21:39:07 GMT

Looks like a straightforward proc transpose

Documentation:

Base SAS(R) 9.3 Procedures Guide, Second Edition

Detailed example:

SAS Learning Module: How to reshape data wide to long using proc transpose

Re: Transpose Variables

Costasg — Thu, 19 Mar 2015 13:37:49 GMT

Hi Reeza,

Thanks for your reply. With only one variable, it is easy to transpose.The code would look something like that:

proc sort data=data; by company_name;run;

proc transpose data=data out=data2;
by company_name;

run;

data data3;
set data2 (rename=(col1=Interest_paid));
year=substr(_NAME_,5);
drop _name_ ;
run;

However, I have more than 100 variables, such as Interest paid, Dividend, etc., which have the form of Interest paid 2015, Interest paid 2014.......Interest paid 2005, Dividend 2015......Dividend 2005, etc.

So, what I want to do is create a Year Variable and then have one Variable for Interest paid, Dividend, etc.

Is there any way to do that?

I steel cannot upload any data but this is what I want:

HAVE:

Company name

Country

Region

BvD ID number

ISIN number

Date of incorporation

Accounting practice

2013

Accounting practice

2012

Accounting practice

2011

Accounting practice

2010

Operating revenue (Turnover)

th GBP

2013

Operating revenue (Turnover)

th GBP

2012

Operating revenue (Turnover)

th GBP

2011

Operating revenue (Turnover)

th GBP

2010

P/L before tax

th GBP

2013

P/L before tax

th GBP

2012

P/L before tax

th GBP

2011

P/L before tax

th GBP

2010

BP P.L.C.

UNITED KINGDOM

London SW1Y

GB00102498

GB0007980591

14/04/1909

IFRS

239,218,000

238,871,000

248,673,000

197,310,000

18,246,000

11,571,000

24,988,000

-3,082,000

VOLKSWAGEN AKTIENGESELLSCHAFT

GERMANY

Wolfsburg

DE2070000543

DE0007664039

28/05/1937

IFRS

173,320,402

169,866,478

141,486,262

114,920,054

10,408,570

21,311,069

15,838,789

7,683,377

WANT:

	Company name	Country	Region	BvD ID number	ISIN number	Date of incorporation	YEAR	Accounting practice	Operating revenue (Turnover) th GBP	P/L before tax th GBP
1	BP P.L.C.	UNITED KINGDOM	London SW1Y	GB00102498	GB0007980591	14/04/1909	2010	IFRS	197,310,000	-3,082,000
1	BP P.L.C.	UNITED KINGDOM	London SW1Y	GB00102498	GB0007980591	14/04/1909	2011	IFRS	248,673,000	24,988,000
1	BP P.L.C.	UNITED KINGDOM	London SW1Y	GB00102498	GB0007980591	14/04/1909	2012	IFRS	238,871,000	11,571,000
1	BP P.L.C.	UNITED KINGDOM	London SW1Y	GB00102498	GB0007980591	14/04/1909	2013	IFRS	239,218,000	18,246,000
2	VOLKSWAGEN AKTIENGESELLSCHAFT	GERMANY	Wolfsburg	DE2070000543	DE0007664039	28/05/1937	2010	IFRS	114,920,054	7,683,377
2	VOLKSWAGEN AKTIENGESELLSCHAFT	GERMANY	Wolfsburg	DE2070000543	DE0007664039	28/05/1937	2011	IFRS	141,486,262	15,838,789
2	VOLKSWAGEN AKTIENGESELLSCHAFT	GERMANY	Wolfsburg	DE2070000543	DE0007664039	28/05/1937	2012	IFRS	169,866,478	21,311,069
2	VOLKSWAGEN AKTIENGESELLSCHAF	GERMANY	Wolfsburg	DE2070000543	DE0007664039	28/05/1937	2013	IFRS	173,320,402	10,408,570

Re: Transpose Variables

Astounding — Thu, 19 Mar 2015 14:03:56 GMT

Costasg,

There's more than one way to do this, but they consume different types of resources.

If your data set is large, and you want the fastest-running program, you could write a DATA step with 100 arrays. That represents a fair amount of tedious programming, however.

If your data set is small, and you don't mind processing it 100 times, you could write a macro to transpose one set of variables at a time. (While there would be 100 macro calls, they could be partially automated based on variable names that end with "_2013".)

I know that's not quite enough to program with, but which is the approach that makes sense to you?

Good luck.

Re: Transpose Variables

Reeza — Thu, 19 Mar 2015 14:08:45 GMT

See Example 3 below which illustrates the array method.

SAS Learning Module: Reshaping wide to long using a data step

You have different variable types (char/numeric) otherwise you could do two transposes to get what you want, similar to the question here. Perhaps you could do it twice, once for character variables and one for numeric?

data - SAS transpose using a part of the name - Stack Overflow

Re: Transpose Variables

byronk — Thu, 19 Mar 2015 15:39:36 GMT

You could use multipal "set" statements to stack the data. I'm certain there's a more efficient way, but this is pretty straight forward if your variable naming is fairly conventional:

*** test data ***;

data t;

comp="a";

country='asd f';

region='qwerty';

int_2005=1005; Div_2005=2312;

int_2006=1006.05; Div_2006=34;

int_2007=1007.; Div_2007=54;

int_2008= 008; Div_2008=65;

int_2009=1009; Div_2009=34;

int_2010=.010; Div_2010=216;

int_2011=101.; Div_2011=1344;

int_2012=1.12; Div_2012=45;

int_2013=10.3; Div_2013=452;

output;

comp="b";

output;

comp="c";

output;

run;

*** view test ***;

proc print data=t; run;

*** use multiple set statements to stack the observations ***;

%macro lp;

data tp;

%do I=2005 %to 2013;

set t(keep=comp country region int_&i. div_&i rename=(int_&i.=Int Div_&&i.=Div));

year=&i.;

output;

%end;

run;

%mend lp;

%lp;

*** view output ***;

proc print data=tp; run;

Re: Transpose Variables

Costasg — Fri, 20 Mar 2015 18:05:00 GMT

Many thanks to everyone for the replies. I decided to go with the arrays.