Help using Base SAS procedures

Transpose Variables

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 75
Accepted Solution

Transpose Variables

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 nameCountryRegionInterest 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 PLCUNITED KINGDOMLondon SE1991.0001.081.000883.000636.000336.000814.000556.000587.000622.000
2.BP P.L.C.UNITED KINGDOMLondon SW1Y645.000692.000802.000747.000687.0001.067.000557.000367.000359.000
3.VITOL HOLDING B.V.NETHERLANDSRotterdamn.a.n.a.n.a.n.a.n.a.n.a.202.317170.75541.437
4.VOLKSWAGEN AKTIENGESELLSCHAFTGERMANYWolfsburg1.981.3882.128.7661.713.0931.831.5722.017.4651.732.7031.210.2151.063.1821.048.908
5.TOTAL SAFRANCEHauts-de-Seine1.004.093978.262955.717744.931778.3431.264.9211.511.4831.346.0711.009.857
6.GLENCORE PLCUNITED KINGDOMJersey1.075.000843.000763.000777.000n.a.n.a.n.a.n.a.n.a.
7.GLENCORE INTERNATIONAL AGSWITZERLANDZugn.a.868.821767.091777.663527.324n.a.n.a.n.a.n.a.
8.E D F TRADING LIMITEDUNITED KINGDOMLondon SW1E41.00038.00029.00021.00038.00042.00012.00016.0002.000
9.E.ON RUHRGAS AUSTRIA GMBHAUSTRIAWienn.a.2.176.425n.a.n.a.n.a.n.a.n.a.n.a.n.a.
10.E.ON SEGERMANYDüsseldorf2.132.128n.a.n.a.n.a.1.017.6282.913.6131.459.3121.484.1641.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.


Accepted Solutions
Solution
‎03-19-2015 10:08 AM
Super User
Posts: 17,784

Re: Transpose Variables

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

View solution in original post


All Replies
Super User
Posts: 17,784

Re: Transpose Variables

Frequent Contributor
Posts: 75

Re: Transpose Variables

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 nameCountryRegionBvD ID numberISIN numberDate of incorporationAccounting 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
1BP P.L.C.UNITED KINGDOMLondon SW1YGB00102498GB000798059114/04/1909IFRSIFRSIFRSIFRS239,218,000238,871,000248,673,000197,310,00018,246,00011,571,00024,988,000-3,082,000
2VOLKSWAGEN AKTIENGESELLSCHAFTGERMANYWolfsburgDE2070000543DE000766403928/05/1937IFRSIFRSIFRSIFRS173,320,402169,866,478141,486,262114,920,05410,408,57021,311,06915,838,7897,683,377

WANT:

Company nameCountryRegionBvD ID numberISIN numberDate of incorporationYEARAccounting practiceOperating revenue (Turnover)

    th GBP
P/L before tax

    th GBP
1BP P.L.C.UNITED KINGDOMLondon SW1YGB00102498GB000798059114/04/19092010IFRS197,310,000-3,082,000
1BP P.L.C.UNITED KINGDOMLondon SW1YGB00102498GB000798059114/04/19092011IFRS248,673,00024,988,000
1BP P.L.C.UNITED KINGDOMLondon SW1YGB00102498GB000798059114/04/19092012IFRS238,871,00011,571,000
1BP P.L.C.UNITED KINGDOMLondon SW1YGB00102498GB000798059114/04/19092013IFRS239,218,00018,246,000
2VOLKSWAGEN AKTIENGESELLSCHAFTGERMANYWolfsburgDE2070000543DE000766403928/05/19372010IFRS114,920,0547,683,377
2VOLKSWAGEN AKTIENGESELLSCHAFTGERMANYWolfsburgDE2070000543DE000766403928/05/19372011IFRS141,486,26215,838,789
2VOLKSWAGEN AKTIENGESELLSCHAFTGERMANYWolfsburgDE2070000543DE000766403928/05/19372012IFRS169,866,47821,311,069
2VOLKSWAGEN AKTIENGESELLSCHAFGERMANYWolfsburgDE2070000543DE000766403928/05/19372013IFRS173,320,40210,408,570
Super User
Posts: 5,079

Re: Transpose Variables

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.

Solution
‎03-19-2015 10:08 AM
Super User
Posts: 17,784

Re: Transpose Variables

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

Occasional Contributor
Posts: 6

Re: Transpose Variables

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

Frequent Contributor
Posts: 75

Re: Transpose Variables

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 420 views
  • 11 likes
  • 4 in conversation