BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Costasg
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

6 REPLIES 6
Costasg
Calcite | Level 5

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
Astounding
PROC Star

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
Super User

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
Fluorite | Level 6

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
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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