Transpose Variables

[Costasg]

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.

[Reeza]

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

[Costasg]

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

1

BP P.L.C.

UNITED KINGDOM

London SW1Y

GB00102498

GB0007980591

14/04/1909

IFRS

IFRS

IFRS

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

2

VOLKSWAGEN AKTIENGESELLSCHAFT

GERMANY

Wolfsburg

DE2070000543

DE0007664039

28/05/1937

IFRS

IFRS

IFRS

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

[Astounding]

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.

[Reeza · Accepted solution]

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

[byronk]

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;

b

[Costasg]

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