STATA experts in Johannesburg to help a PhD student

[Julia Mdzikwa]

I am looking for a STATA experienced individual to assist me with arranging the data extracted from DATASTREAM to a panel data in STATA. Please recommend if you know of any.

[Nicola Tommasi]

Interesting challenge...
Can you provide an example of data?

[Julia Mdzikwa]

TEST22.xlsx


I am trying to arrange data in a panel format in STATA b jut keep on getting error. Please see attached data and the error and help if you can. I am not sure what am I doing wrong.


variable id does not uniquely identify the observations
Your data are currently wide. You are performing a reshape long. You
specified i(tcsy) and j(year). In the current wide form, variable tcsy
should uniquely identify the observations. Remember this picture:

long wide
+---------------+ +------------------+
| i j a b | | i a1 a2 b1 b2 |
|---------------| <--- reshape ---> |------------------|
| 1 1 1 2 | | 1 1 3 2 4 |
| 1 2 3 4 | | 2 5 7 6 8 |
| 2 1 5 6 | +------------------+
| 2 2 7 8 |
+---------------+
Type reshape error for a list of the problem observations.
r(9);

. generate i_tcsy=group(tcsy)
type mismatch
r(109);

. generate i_tcsy, group(tcsy)
=exp required
r(100);

. egen i_tcsy=group(tcsy)

. reshape long yr, i(tcsy) j(year)
(j = 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013
> 2014 2015 2016 2017 2018 2019 2020)
variable id does not uniquely identify the observations
Your data are currently wide. You are performing a reshape long. You
specified i(tcsy) and j(year). In the current wide form, variable tcsy
should uniquely identify the observations. Remember this picture:

long wide
+---------------+ +------------------+
| i j a b | | i a1 a2 b1 b2 |
|---------------| <--- reshape ---> |------------------|
| 1 1 1 2 | | 1 1 3 2 4 |
| 1 2 3 4 | | 2 5 7 6 8 |
| 2 1 5 6 | +------------------+
| 2 2 7 8 |
+---------------+
Type reshape error for a list of the problem observations.
r(9);

. reshape long yr, i(i_tcsy) j(year)
(j = 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013
> 2014 2015 2016 2017 2018 2019 2020)
variable id does not uniquely identify the observations
Your data are currently wide. You are performing a reshape long. You
specified i(i_tcsy) and j(year). In the current wide form, variable i_tcsy
should uniquely identify the observations. Remember this picture:

long wide
+---------------+ +------------------+
| i j a b | | i a1 a2 b1 b2 |
|---------------| <--- reshape ---> |------------------|
| 1 1 1 2 | | 1 1 3 2 4 |
| 1 2 3 4 | | 2 5 7 6 8 |
| 2 1 5 6 | +------------------+
| 2 2 7 8 |
+---------------+
Type reshape error for a list of the problem observations.
r(9);

. reshape long yr, tcsy(i_tcsy) j(year)
option tcsy() not allowed
r(198);

. reshape long yr, i(i_tcsy) j(year)
(j = 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013
> 2014 2015 2016 2017 2018 2019 2020)
variable id does not uniquely identify the observations
Your data are currently wide. You are performing a reshape long. You
specified i(i_tcsy) and j(year). In the current wide form, variable i_tcsy
should uniquely identify the observations. Remember this picture:

long wide
+---------------+ +------------------+
| i j a b | | i a1 a2 b1 b2 |
|---------------| <--- reshape ---> |------------------|
| 1 1 1 2 | | 1 1 3 2 4 |
| 1 2 3 4 | | 2 5 7 6 8 |
| 2 1 5 6 | +------------------+
| 2 2 7 8 |
+---------------+
Type reshape error for a list of the problem observations.
r(9);

. reshape long yr, i(i_id) j(year)
variable i_id not found
r(111);

. reshape long yr, i(c_id) j(year)
(j = 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013
> 2014 2015 2016 2017 2018 2019 2020)
variable id does not uniquely identify the observations
Your data are currently wide. You are performing a reshape long. You
specified i(c_id) and j(year). In the current wide form, variable c_id
should uniquely identify the observations. Remember this picture:

long wide
+---------------+ +------------------+
| i j a b | | i a1 a2 b1 b2 |
|---------------| <--- reshape ---> |------------------|
| 1 1 1 2 | | 1 1 3 2 4 |
| 1 2 3 4 | | 2 5 7 6 8 |
| 2 1 5 6 | +------------------+
| 2 2 7 8 |
+---------------+
Type reshape error for a list of the problem observations.
r(9);

[Andrew Musau]

It appears that your dataset is not a panel as you believe. Can you copy and paste the result of the following:

sort tcsy year
dataex tcsy year

[Jared Greathouse]

When I do

import exc using "https://www.statalist.org/forums/filedata/fetch?id=1661569&d=1650970369", ///
clear ///
first

cls

duplicates tag c_id v_id, generate(dup1)

tab dup

br if dup1 ==1

We see the that you have 76 duplicate observations in terms of company ID and variable ID. So, therein lies the issue.

[Julia Mdzikwa]

Andrew Musau yes the data is not a panel. I am trying to arrange it in a panel format in stata. The data was downloaded from datastream (name of database) in the format as attached (see attachment). I sorted it out of stata until in the format is as sent initially. The data posted initially is imported to stata to finish the reshape so that it is in the panel format. STATA.xlsx

[Andrew Musau]

Once you import the data, run the code in #4 and copy and paste the output here. In this way, we will be able to see your data and give you suggestions. Few people will download Excel attachments due to risk of malware. This is explained in FAQ Advice #12.

[Julia Mdzikwa]

Jared the I think the duplicates are due to the fact that the data is not yet completely arranged in a panel format in stata, which is what I am trying to do. When I try to drop the duplicates, stata requires me to use FORCE and this drops almost 90% of the data. I am so frustrated as I cannot progress to running tests/regression test. I have been trying to get data properly arranged in a panel format for about three months and I am not getting it right.

[Julia Mdzikwa]

Thank you Andrew. I am new in this forum, joined yesterday and still finding my way. Where do I find the code you are referring to n(#4) or the codes in general. Your assistance will be appreciated.

[Julia Mdzikwa]

The link for FAQ takes me to number 4 which addresses the question "Can I post an elementary question"?

[FernandoRios]

Hi Julia
While im usually in favor and inclined to do ALL data cleaning in Stata, ocassionally, I think it is necessary to do some manual precleaning in other software, like Excel.
For example, the file that you provided has the following:

Start	1999
End	2020
Frequency	Y
Name	#ERROR	#ERROR	#ERROR	ABSA GROUP LTD - NET SALES OR REVENUES	ABSA GROUP LTD - TOTAL ASSETS	ABSA GROUP LTD - PROPERTY, PLANT & EQUIP - NET	#ERROR	#ERROR	ABSA GROUP LTD - FUNDS FROM OPERATIONS
Code				314909(WC01001)~U$	314909(WC02999)~U$	314909(WC02501)~U$			314909(WC04201)~U$
CURRENCY				U$	U$	U$			U$
1999	$$ER: E100,INVALID CODE OR EXPRESSION ENTERED	$$ER: E100,INVALID CODE OR EXPRESSION ENTERED	$$ER: E100,INVALID CODE OR EXPRESSION ENTERED	5410086	27936521	423840	$$ER: 4540,NO DATA VALUES FOUND	$$ER: E100,INVALID CODE OR EXPRESSION ENTERED	638243
2000				4529865	28773510	472800			650667
2001				3668479	24921035	355386			-1577494
2002				2831729	21701485	224351			493889
2003				4494681	30672011	298117			802850
2004				5502127	42885043	363154			1224963
2005				7340521	68481068	580180			1033262
2006				8627851	81271207	615712			1599866
2007				9667458	88015569	633198			1817730
2008				13698300	1.08E+08	870383			3461904
2009				8556615	70712797	651172			1400326

However, all the Bolded cells have information that you do not need. So delete it manually.
also, the Red ones should be easily identifiable names (say country name? or code? or just generic variable name with sequential numbers? var_1 var_2 var_3)

You will need to make those modifications by hand before trying to do anythng else.

F

[Andrew Musau]

I ran

import excel "TEST22.xlsx", sheet("Sheet1") firstrow clear
dataex Company Variables v_id yr1999-yr2015 in 1/96

The imported dataset is below. There are some duplicate observations of company and year where one set is nonmissing and the other is entirely missing. I resolve this by collapsing the data choosing the first nonmissing value.

* Example generated by -dataex-. For more info, type help dataex
clear
input str21 Company str44 Variables byte v_id long(yr1999 yr2000 yr2001 yr2002 yr2003 yr2004 yr2005 yr2006 yr2007 yr2008 yr2009 yr2010 yr2011 yr2012 yr2013 yr2014 yr2015)
"ADCOCK INGRAM HOLD " " ACCOUNTS PAYABLE"               1        .        .        .        .        .        .        .        .        .    46507    31582    54254    82563    81464    89892    56180    58740
"ADCOCK INGRAM HOLD " " DEPRECIATION/DEPLETION/AMORT"   2        .        .        .        .        .        .        .        .        .     5157     8159    13379    14332    15001    22565    18852    17143
"ADCOCK INGRAM HOLD " " FUNDS FROM OPERATIONS"          3        .        .        .        .        .        .        .        .        .    62115    91232   135195   121791   116213   105889    -4295    48820
"ADCOCK INGRAM HOLD " " NET SALES OR REVENUES"          4        .        .        .        .        .        .        .        .        .   246591   394799   584789   629688   590499   607351   433796   483301
"ADCOCK INGRAM HOLD " " OPERATING EXPENSES - TOTAL"     5        .        .        .        .        .        .        .        .        .   176797   291857   427746   476990   478407   508000   434473   445747
"ADCOCK INGRAM HOLD " " OPERATING INCOME"               6        .        .        .        .        .        .        .        .        .    69794   102943   157043   152698   112092    99351     -677    37553
"ADCOCK INGRAM HOLD " " PROPERTY, PLANT & EQUIP - NET"  7        .        .        .        .        .        .        .        .        .    63375    59119   112920   164232   200312   197013   139886   130331
"ADCOCK INGRAM HOLD " " PROPERTY, PLANT & EQUIP-GROSS"  8        .        .        .        .        .        .        .        .        .    94448    87371   159377   222072   258663   257848   188876   188650
"ADCOCK INGRAM HOLD " " RECEIVABLES(NET)"               9        .        .        .        .        .        .        .        .        .   120432    97008   144776   161783   166246   180625   112827   124803
"ADCOCK INGRAM HOLD " " TOTAL ASSETS"                  10        .        .        .        .        .        .        .        .        .   359257   331941   605239   720275   659896   758884   485767   476088
"ADCOCK INGRAM HOLD " " TOTAL INVENTORIES"             11        .        .        .        .        .        .        .        .        .    79436    57537    94716   122226   122762   173815    99555   105569
"ADVTECH LTD "        " ACCOUNTS PAYABLE"               1     2680        .        .     4204     5155     9520    13859    17769    15495    17247    15061    16580    24333    26204    28964    22201    26454
"ADVTECH LTD "        " DEPRECIATION/DEPLETION/AMORT"   2     1744     2932     4061     2344     2400     2977     4309     4393     4599     6396     5791     8744     8809     8679     7796     7595     9581
"ADVTECH LTD "        " FUNDS FROM OPERATIONS"          3     4768     8993     3878     2077     6420     9526    18079    17562    19441    21733    21643    27286    27585    30865    25875    23191    29531
"ADVTECH LTD "        " NET SALES OR REVENUES"          4    34194    89864    65416    41325    55423    79683   111133   136299   132231   167935   135636   193597   227015   216620   196995   173847   236712
"ADVTECH LTD "        " OPERATING EXPENSES - TOTAL"     5    31656        .        .    38942    50329    71818    96216   116869   110225   140013   114035   166917   194453   190942   172269   150773   197521
"ADVTECH LTD "        " OPERATING INCOME"               6     2539     7942     3279     2383     5094     7866    14917    19430    22007    27922    21601    26680    32562    25678    24726    23074    39191
"ADVTECH LTD "        " PROPERTY, PLANT & EQUIP - NET"  7    39317    46966    38398    27308    35796    46759    61362    64996    60620    78532    62742    89852   114936   119377   133680   129499   221929
"ADVTECH LTD "        " PROPERTY, PLANT & EQUIP-GROSS"  8    42463        .        .    33588    45280    61358    83083    87215    83193   107498    87932   129675   160520   165816   179652   171543   265055
"ADVTECH LTD "        " RECEIVABLES(NET)"               9     2903    14626     6029     2830     2167     4860     4832     3823     6679    10150     8367    10390    16302    14251    12436    13823    16872
"ADVTECH LTD "        " TOTAL ASSETS"                  10    61015   111382    80119    31550    38872    52739    73721    81046    86832   109418    89503   126159   159290   168589   180109   175251   376150
"ADVTECH LTD "        " TOTAL INVENTORIES"             11      162      585      539      338      544      731     1014      851      445      833      440      395       85       64      190      117      332
"AECI LIMITED "       " ACCOUNTS PAYABLE"               1   192549   184839   128436    79736    91158   143052   181569   241523   168670   279566   143916   166192   271892   363088   347638   297244   335525
"AECI LIMITED "       " DEPRECIATION/DEPLETION/AMORT"   2    51490    38103    35120    24615    33999    45866    35641    36614    22526    29583    26319    43721    55425    60600    59891    49046    51317
"AECI LIMITED "       " FUNDS FROM OPERATIONS"          3    95033    51398    45517    52835    64803    96627   152652   153189    99856   133193    28685   148809   178151   156893   172648   141288   129035
"AECI LIMITED "       " NET SALES OR REVENUES"          4  1210428   974299   855184   687295   873814  1106243  1474071  1676707  1170385  1805260  1055616  1523519  1894198  1915071  1778007  1521138  1612585
"AECI LIMITED "       " OPERATING EXPENSES - TOTAL"     5  1123342   912848   823487   634724   805702  1018146  1330160  1498397  1071079  1659729   973900  1384586  1707422  1742899  1622089  1377511  1463706
"AECI LIMITED "       " OPERATING INCOME"               6    87086    61451    31697    52571    68112    88097   143910   178310    99306   145531    81717   138933   186776   172171   155919   143628   148880
"AECI LIMITED "       " PROPERTY, PLANT & EQUIP - NET"  7   320032   291528   242165   152439   194865   231988   289670   322633   215232   340835   321347   469342   526111   479281   418906   364108   375565
"AECI LIMITED "       " PROPERTY, PLANT & EQUIP-GROSS"  8   601654   519496   431839   286681   380033   482015   622210   675641   395852   723730   514649   748129   880999   849559   793534   687900   758997
"AECI LIMITED "       " RECEIVABLES(NET)"               9   207119   197649   186251   116132   146035   198567   298916   368114   270860   439257   202567   256268   367896   332659   352323   282126   327570
"AECI LIMITED "       " TOTAL ASSETS"                  10   949667   936034   745893   482988   641413   771475  1057975  1255889  1100610  1488681   954579  1311367  1703746  1621827  1553052  1280769  1509952
"AECI LIMITED "       " TOTAL INVENTORIES"             11   155463   167815   156076   109714   133485   162210   230660   284541   217018   391869   180093   249157   365351   368095   344627   259088   293563
"AFRICAN RAINBOW "    " ACCOUNTS PAYABLE"               1    60265    62910    39811    47472    31945    40413    65735    35958    68814   130249    64664   112463   129937   116835   214695   102951    56824
"AFRICAN RAINBOW "    " DEPRECIATION/DEPLETION/AMORT"   2        .        .        .        .        .        .        .        .        .        .        .        .        .        .        .        .        .
"AFRICAN RAINBOW "    " DEPRECIATION/DEPLETION/AMORT"   2    18874    27077    20540    21099    44153    30484    71619    72244    55903    75850    77577   129978   157225   168833   188820    85403    91531
"AFRICAN RAINBOW "    " FUNDS FROM OPERATIONS"          3        .        .        .        .        .        .        .        .        .        .        .        .        .        .        .        .        .
"AFRICAN RAINBOW "    " FUNDS FROM OPERATIONS"          3    92053   133928  -162035    67165    76326    93550   287484   177653   347091   948897   323417   459597   762797   774964   767102   393626   303442
"AFRICAN RAINBOW "    " NET SALES OR REVENUES"          4        .        .        .        .        .        .        .        .        .        .        .        .        .        .        .        .        .
"AFRICAN RAINBOW "    " NET SALES OR REVENUES"          4   412085   475718   355767   355779   558584   543263   922135   758886   844996  1765162   994994  1451484  2105717  2250683  2213197   900282   809790
"AFRICAN RAINBOW "    " OPERATING EXPENSES - TOTAL"     5        .        .        .        .        .        .        .        .        .        .        .        .        .        .        .        .        .
"AFRICAN RAINBOW "    " OPERATING EXPENSES - TOTAL"     5   274337   390433   317984   357274   540101   476421   697863   603726   534715   927025   719877  1114490  1425491  1691286  1701052   834318   819930
"AFRICAN RAINBOW "    " OPERATING INCOME"               6        .        .        .        .        .        .        .        .        .        .        .        .        .        .        .        .        .
"AFRICAN RAINBOW "    " OPERATING INCOME"               6   137748    85286    37783    -1495    18483    66842   224271   155159   310281   838136   275117   336994   680226   559397   512145    65964   -10141
"AFRICAN RAINBOW "    " PROPERTY, PLANT & EQUIP - NET"  7   506290   584514   718126   471647   509753   453208   635659   581561   946637  1265196  1133588  1745679  1924596  2166973  2301528  1057588  1068121
"AFRICAN RAINBOW "    " PROPERTY, PLANT & EQUIP-GROSS"  8   621687   675475   807385   710240   624299   587591   853710   756423  1153216  1544902  1399044  2225030  2553215  2884033  3127518  1471913  1623426
"AFRICAN RAINBOW "    " RECEIVABLES(NET)"               9        .        .        .        .        .        .        .        .        .        .        .        .        .        .        .        .        .
"AFRICAN RAINBOW "    " RECEIVABLES(NET)"               9   110099   104256    84314    93187   106788   162489   256886   190460   255339   583807   154365   404288   450750   466314   522963   296614   222489
"AFRICAN RAINBOW "    " TOTAL ASSETS"                  10        .        .        .        .        .        .        .        .        .        .        .        .        .        .        .        .        .
"AFRICAN RAINBOW "    " TOTAL ASSETS"                  10   803309  1526547  1149585   769053   815628  1601542  1966660  2395202  2492134  3485178  2510354  3712202  4555859  4533849  4215156  3246649  3035115
"AFRICAN RAINBOW "    " TOTAL INVENTORIES"             11        .        .        .        .        .        .        .        .        .        .        .        .        .        .        .        .        .
"AFRICAN RAINBOW "    " TOTAL INVENTORIES"             11   111258    95014    91541    85802   102224   127810   192329   116082   117162   172590   182754   241519   305685   315584   359349    84053    74484
"AFRIMAT LTD "        " ACCOUNTS PAYABLE"               1        .        .        .        .        .        .        .     5242     6481     8217     7222     8490    10628     9909     8656    15694    13510
"AFRIMAT LTD "        " DEPRECIATION/DEPLETION/AMORT"   2        .        .        .        .        .        .        .        .     2186     4746     3761     5176     6439     5957     6456     8720     6697
"AFRIMAT LTD "        " FUNDS FROM OPERATIONS"          3        .        .        .        .        .        .        .        .    10544    17568     8384    14905    17573    19095    18392    22408    22964
"AFRIMAT LTD "        " NET SALES OR REVENUES"          4        .        .        .        .        .        .        .    67883    47941    85757    67728   102457   120817   127894   149180   171092   174722
"AFRIMAT LTD "        " OPERATING EXPENSES - TOTAL"     5        .        .        .        .        .        .        .    54999    38895    67868    58847    88541   105685   112157   131839   150308   150748
"AFRIMAT LTD "        " OPERATING INCOME"               6        .        .        .        .        .        .        .    12884     9045    17889     8882    13916    15132    15737    17342    20784    23974
"AFRIMAT LTD "        " PROPERTY, PLANT & EQUIP - NET"  7        .        .        .        .        .        .        .    27497    26170    43417    37708    50735    57119    54682    56168    59602    63368
"AFRIMAT LTD "        " PROPERTY, PLANT & EQUIP-GROSS"  8        .        .        .        .        .        .        .        .    31557    60519    49113    70843    94712    93605    99003   115749   120322
"AFRIMAT LTD "        " RECEIVABLES(NET)"               9        .        .        .        .        .        .        .     8387     9728    16262    13044    16948    22000    20675    22146    27729    25178
"AFRIMAT LTD "        " TOTAL ASSETS"                  10        .        .        .        .        .        .        .    60296    61023    92592    74381   110450   134029   127745   133641   132682   134839
"AFRIMAT LTD "        " TOTAL INVENTORIES"             11        .        .        .        .        .        .        .     3636     4932     8369     7433     9068    10682     9222     9981    10166    11085
"ALLIED ELECTRONICS " " ACCOUNTS PAYABLE"               1   221045   212524   228918   183515   194571   234085   330018   410639   370304   459867   325783   425095   492178   452448   490285   466520   351524
"ALLIED ELECTRONICS " " DEPRECIATION/DEPLETION/AMORT"   2    17218    20068    25828    17775    28152    36218    65398    34972    32278    38135    43175    67162    81299    72155    51415    40137    48607
"ALLIED ELECTRONICS " " FUNDS FROM OPERATIONS"          3   111326    65912    83963    57660   107344    84741   150635   145636   191882   252787   150126   190160   226082   161387    99261   125269    97475
"ALLIED ELECTRONICS " " NET SALES OR REVENUES"          4  1157740  1130370  1137748   870288  1300320  1404653  2052065  2293569  2352309  3004701  2441452  2941422  3225099  3025262  2762481  2499264  1930801
"ALLIED ELECTRONICS " " OPERATING EXPENSES - TOTAL"     5  1070590  1044410  1070400   818916  1202311  1324247  1926480  2126916  2150949  2742100  2269344  2734933  3008207  2842178  2625188  2378494  1865672
"ALLIED ELECTRONICS " " OPERATING INCOME"               6    87150    85959    67348    51372    98008    80406   125585   166653   201360   262601   172108   206490   216892   183085   137293   120770    65129
"ALLIED ELECTRONICS " " PROPERTY, PLANT & EQUIP - NET"  7   102127    98627    85251    61701    75969    93830   142397   148592   131035   177217   218930   320796   341173   295298   203207   182504   165053
"ALLIED ELECTRONICS " " PROPERTY, PLANT & EQUIP-GROSS"  8   240789   243693   250449   186390   227606   283867   413237   434282   380743   462952   446338   636720   734237   721425   537462   475969   450223
"ALLIED ELECTRONICS " " RECEIVABLES(NET)"               9   178830   200140   236869   178154   212931   215627   299084   324439   342559   466036   352398   440502   455558   479538   457718   518085   441131
"ALLIED ELECTRONICS " " TOTAL ASSETS"                  10   826082   802096   780786   611340   691482   832863  1120347  1220423  1135635  1511814  1316046  1623340  1727358  1558787  1433938  1436816  1309319
"ALLIED ELECTRONICS " " TOTAL INVENTORIES"             11   209174   181649   142600    97261   122921   126552   194682   214925   276492   298633   233026   263116   330286   317766   295888   280416   255272
"ANGLO AMERICAN "     " ACCOUNTS PAYABLE"               1    74586   183591    62430    61327   216451   297529   513806   885969   479912   441921   351215   588785   689133   515102   575717   442672   379149
"ANGLO AMERICAN "     " DEPRECIATION/DEPLETION/AMORT"   2    57401    70823    67553    70083   139943   219864   382303   410360   390632   475290   415386   585229   673156   631809   544042   440692   449349
"ANGLO AMERICAN "     " FUNDS FROM OPERATIONS"          3   548575  1474889  1294326   541432   336941   720687  1264190  2976451  2077740  2803929   532983  1502185  1743052   684577  1103810   547963   779454
"ANGLO AMERICAN "     " NET SALES OR REVENUES"          4  1410261  2519168  2266827  1718914  1836891  2694220  3856322  6428905  6402850  7117430  3616341  6061021  7227417  5499986  5844606  5004647  5229144
"ANGLO AMERICAN "     " OPERATING EXPENSES - TOTAL"     5   904286  1111631  1079459   914354  1433344  2103063  2921814  3788748  3871983  4957595  3422252  4987354  6131505  6313211  5210447  4850130  4869665
"ANGLO AMERICAN "     " OPERATING INCOME"               6   505976  1407537  1187368   804561   403547   591157   934508  2640157  2530868  2159835   194090  1073667  1095911  -813225   634158   154517   359479
"ANGLO AMERICAN "     " PROPERTY, PLANT & EQUIP - NET"  7  1064203  1279300  1384675  1357764  2487157  3339521  4350273  4925631  4980147  6529416  5259551  7177487  8121282  6816886  5923123  4952541  4057865
"ANGLO AMERICAN "     " PROPERTY, PLANT & EQUIP-GROSS"  8  1336570  1913056  1710406  1650493  2990510  4150137  5690941  6642499  6716565  8748837  7163879 10193841 11959164 10862082  9787629  8421741  8350195
"ANGLO AMERICAN "     " RECEIVABLES(NET)"               9   125844   261937   246831   142215   260889   291041   406546   801756   583201   552542   284974   393489   433501   347681   404184   377427   277040
"ANGLO AMERICAN "     " TOTAL ASSETS"                  10  1766800  2837902  2602713  1836286  3170509  4551018  6016908  7604077  7423933  9627492  7473891 11035733 12340492 11034767  9929160  8099209  6465728
"ANGLO AMERICAN "     " TOTAL INVENTORIES"             11   203427   219019   168171   131674   278333   446398   741793   870272   874939  1411008  1113084  1653760  1770906  2046157  2193567  1570454  1448669
"ANGLO AMERICAN PLC " " ACCOUNTS PAYABLE"               1  1009341  1917990  1483726  1349315  2308690  2647502  3436212  2979880  2514398  4355641  2539844  2839980  3069907  2607784  2256869  2045818  1642936
"ANGLO AMERICAN PLC " " DEPRECIATION/DEPLETION/AMORT"   2   757263  1006615  1020382  1028580  1505114  2146555  2532758  1958423  1364430  1616030  1538810  2014220  1941771  2256179  2661470  2597846  2350045
"ANGLO AMERICAN PLC " " FUNDS FROM OPERATIONS"          3  2103050  3519898  3379003  3020345  2878915  4969042  7219554  8326184  7059317  8179744  4014286  7877396  8947742  5512804  7319547  5640864  3436730
"ANGLO AMERICAN PLC " " NET SALES OR REVENUES"          4 11912482 16079807 14967628 14148816 18539856 24672019 30540436 31811872 24858400 28177174 18606663 29347366 30187772 28348609 29603051 27144529 20189064
"ANGLO AMERICAN PLC " " OPERATING EXPENSES - TOTAL"     5 10597562 13696689 12702137 11953391 16720389 21758484 25115914 23249061 16552747 20010281 14603081 20375217 20907352 23044764 25473636 22772007 19670889
"ANGLO AMERICAN PLC " " OPERATING INCOME"               6  1314921  2383118  2265491  2195425  1819466  2913535  5424523  8562811  8305653  8166893  4003581  8972149  9280420  5303844  4129415  4372522   518174
"ANGLO AMERICAN PLC " " PROPERTY, PLANT & EQUIP - NET"  7  9786797 12990670 10782542 14474707 22264063 29542594 33722624 21755045 23244851 40429596 30417640 41142508 41480064 43532899 39624088 40762730 30226966
"ANGLO AMERICAN PLC " " PROPERTY, PLANT & EQUIP-GROSS"  8 15130849 18147808 14762172 18956079 29423011 38977639 48289842 33139412 32764209 52789052 41913051 56422386 57026010 60868240 59254264 68372262 61124373
"ANGLO AMERICAN PLC " " RECEIVABLES(NET)"               9  1894186  3173201  2506916  1954361  3598196  4744073  5295578  4629180  3613583  4348799  2904531  3933393  3738924  3437138  3210596  2686791  2071528
"ANGLO AMERICAN PLC " " TOTAL ASSETS"                  10 26615313 32879507 24879941 28852222 39365538 49504114 56452215 42110103 43738283 67708798 48411734 68485068 73563205 75449043 66637777 68503635 52144349
"ANGLO AMERICAN PLC " " TOTAL INVENTORIES"             11  1472341  1921287  1384611  1588356  2505951  3224984  3908171  2715956  2314906  3697437  2775767  3724632  3597755  4832269  4571973  5000652  4133873
end

*START HERE
duplicates drop *, force
collapse (firstnm) yr* Variables, by(Company v_id)
replace Variables=strtoname(Variables)
reshape long yr, i(Company v_id) j(year)
bys Company v_id: replace Variables= Variables[1]
drop v_id
reshape wide yr, i(Company year) j(Variables) string
rename yr_* *
encode Company, g(company)
order Company company year
xtset company year

Res.:

. l company-OPERATING_INCOME in 1/68, sepby(company)

     +---------------------------------------------------------------------------------------------+
     |            company   year   ACCOUN~E   DEPREC~T   FUNDS_~S   NET_SA~S   OPERAT~L   OPERAT~E |
     |---------------------------------------------------------------------------------------------|
  1. | ADCOCK INGRAM HOLD   1999          .          .          .          .          .          . |
  2. | ADCOCK INGRAM HOLD   2000          .          .          .          .          .          . |
  3. | ADCOCK INGRAM HOLD   2001          .          .          .          .          .          . |
  4. | ADCOCK INGRAM HOLD   2002          .          .          .          .          .          . |
  5. | ADCOCK INGRAM HOLD   2003          .          .          .          .          .          . |
  6. | ADCOCK INGRAM HOLD   2004          .          .          .          .          .          . |
  7. | ADCOCK INGRAM HOLD   2005          .          .          .          .          .          . |
  8. | ADCOCK INGRAM HOLD   2006          .          .          .          .          .          . |
  9. | ADCOCK INGRAM HOLD   2007          .          .          .          .          .          . |
 10. | ADCOCK INGRAM HOLD   2008      46507       5157      62115     246591     176797      69794 |
 11. | ADCOCK INGRAM HOLD   2009      31582       8159      91232     394799     291857     102943 |
 12. | ADCOCK INGRAM HOLD   2010      54254      13379     135195     584789     427746     157043 |
 13. | ADCOCK INGRAM HOLD   2011      82563      14332     121791     629688     476990     152698 |
 14. | ADCOCK INGRAM HOLD   2012      81464      15001     116213     590499     478407     112092 |
 15. | ADCOCK INGRAM HOLD   2013      89892      22565     105889     607351     508000      99351 |
 16. | ADCOCK INGRAM HOLD   2014      56180      18852      -4295     433796     434473       -677 |
 17. | ADCOCK INGRAM HOLD   2015      58740      17143      48820     483301     445747      37553 |
     |---------------------------------------------------------------------------------------------|
 18. |        ADVTECH LTD   1999       2680       1744       4768      34194      31656       2539 |
 19. |        ADVTECH LTD   2000          .       2932       8993      89864          .       7942 |
 20. |        ADVTECH LTD   2001          .       4061       3878      65416          .       3279 |
 21. |        ADVTECH LTD   2002       4204       2344       2077      41325      38942       2383 |
 22. |        ADVTECH LTD   2003       5155       2400       6420      55423      50329       5094 |
 23. |        ADVTECH LTD   2004       9520       2977       9526      79683      71818       7866 |
 24. |        ADVTECH LTD   2005      13859       4309      18079     111133      96216      14917 |
 25. |        ADVTECH LTD   2006      17769       4393      17562     136299     116869      19430 |
 26. |        ADVTECH LTD   2007      15495       4599      19441     132231     110225      22007 |
 27. |        ADVTECH LTD   2008      17247       6396      21733     167935     140013      27922 |
 28. |        ADVTECH LTD   2009      15061       5791      21643     135636     114035      21601 |
 29. |        ADVTECH LTD   2010      16580       8744      27286     193597     166917      26680 |
 30. |        ADVTECH LTD   2011      24333       8809      27585     227015     194453      32562 |
 31. |        ADVTECH LTD   2012      26204       8679      30865     216620     190942      25678 |
 32. |        ADVTECH LTD   2013      28964       7796      25875     196995     172269      24726 |
 33. |        ADVTECH LTD   2014      22201       7595      23191     173847     150773      23074 |
 34. |        ADVTECH LTD   2015      26454       9581      29531     236712     197521      39191 |
     |---------------------------------------------------------------------------------------------|
 35. |       AECI LIMITED   1999     192549      51490      95033    1210428    1123342      87086 |
 36. |       AECI LIMITED   2000     184839      38103      51398     974299     912848      61451 |
 37. |       AECI LIMITED   2001     128436      35120      45517     855184     823487      31697 |
 38. |       AECI LIMITED   2002      79736      24615      52835     687295     634724      52571 |
 39. |       AECI LIMITED   2003      91158      33999      64803     873814     805702      68112 |
 40. |       AECI LIMITED   2004     143052      45866      96627    1106243    1018146      88097 |
 41. |       AECI LIMITED   2005     181569      35641     152652    1474071    1330160     143910 |
 42. |       AECI LIMITED   2006     241523      36614     153189    1676707    1498397     178310 |
 43. |       AECI LIMITED   2007     168670      22526      99856    1170385    1071079      99306 |
 44. |       AECI LIMITED   2008     279566      29583     133193    1805260    1659729     145531 |
 45. |       AECI LIMITED   2009     143916      26319      28685    1055616     973900      81717 |
 46. |       AECI LIMITED   2010     166192      43721     148809    1523519    1384586     138933 |
 47. |       AECI LIMITED   2011     271892      55425     178151    1894198    1707422     186776 |
 48. |       AECI LIMITED   2012     363088      60600     156893    1915071    1742899     172171 |
 49. |       AECI LIMITED   2013     347638      59891     172648    1778007    1622089     155919 |
 50. |       AECI LIMITED   2014     297244      49046     141288    1521138    1377511     143628 |
 51. |       AECI LIMITED   2015     335525      51317     129035    1612585    1463706     148880 |
     |---------------------------------------------------------------------------------------------|
 52. |    AFRICAN RAINBOW   1999      60265      18874      92053     412085     274337     137748 |
 53. |    AFRICAN RAINBOW   2000      62910      27077     133928     475718     390433      85286 |
 54. |    AFRICAN RAINBOW   2001      39811      20540    -162035     355767     317984      37783 |
 55. |    AFRICAN RAINBOW   2002      47472      21099      67165     355779     357274      -1495 |
 56. |    AFRICAN RAINBOW   2003      31945      44153      76326     558584     540101      18483 |
 57. |    AFRICAN RAINBOW   2004      40413      30484      93550     543263     476421      66842 |
 58. |    AFRICAN RAINBOW   2005      65735      71619     287484     922135     697863     224271 |
 59. |    AFRICAN RAINBOW   2006      35958      72244     177653     758886     603726     155159 |
 60. |    AFRICAN RAINBOW   2007      68814      55903     347091     844996     534715     310281 |
 61. |    AFRICAN RAINBOW   2008     130249      75850     948897    1765162     927025     838136 |
 62. |    AFRICAN RAINBOW   2009      64664      77577     323417     994994     719877     275117 |
 63. |    AFRICAN RAINBOW   2010     112463     129978     459597    1451484    1114490     336994 |
 64. |    AFRICAN RAINBOW   2011     129937     157225     762797    2105717    1425491     680226 |
 65. |    AFRICAN RAINBOW   2012     116835     168833     774964    2250683    1691286     559397 |
 66. |    AFRICAN RAINBOW   2013     214695     188820     767102    2213197    1701052     512145 |
 67. |    AFRICAN RAINBOW   2014     102951      85403     393626     900282     834318      65964 |
 68. |    AFRICAN RAINBOW   2015      56824      91531     303442     809790     819930     -10141 |
     +---------------------------------------------------------------------------------------------+