reshape long- 3 variables from 1 variable

[Guest]

Dear Stata experts,

I have a problem when creating a panel. I have such a structure:

period	AustraliaAustria01	...	AustraliaCanada99
1990	3.5	....	2.7
....	....	.....	....
2018	3.7	.....	2.9

I would like to reshape data into the long format so that I would have:

period	exporter	importer	ISIC	export
1990	Australia	Austria	01	3.5
...	.....	.....	....	....
2018	Australia	Canada	99	2.9

I tried a code:

reshape long AustraliaAustria-AustraliaCanada,i(period) j(ISIC)

I got an error. The other issue is that I have few time AustraliaAustria and AustraliaCanada since there are different numbers after it, e.g. AustraliaAustria01 AustraliaAustria02 etc.

Here is sample of my data:

* Example generated by -dataex-. For more info, type help dataex
clear
input double(AustraliaAustria01 AustraliaAustria011 AustraliaAustria0111)
3.09566901071224  .57895066182819  .294643535250431
 4.5952602023237 2.25751780793534  2.11941353857843
 5.2282254216153 3.62389907526694   3.2407176217882
3.60528197241182 2.27059248575925  2.15936564523635
4.20129878529848 2.61912210318222  2.48602899558369
1.66267308728776 .718284669540413  .601226761357698
1.67174006197263 .782547191550645  .713766766181845
1.46324874669685 1.14965136733421  1.02237855424437
1.49118616589432 1.33347393623139  1.17672043293244
.797078970751392  .71046188236338  .620047433227721
.685350935246251 .614281599995358  .548381828240676
.790855012105365 .575420760492461  .517316716681913
.678040814956461 .572556585585948  .481116637902978
.962200827340452 .648574348691287  .387374677722107
.539462037877982  .46406333019581  .289660600577625
.829157935583996 .501237842646939 .0739293580848232
.892993578681696 .297647331955208 .0716496026031399
 1.1831879757206 .330713317689642 .0790955954086102
.722686752070487 .254740682283988 .0355505518170268
1.25267905943902 .785066710532028 .0507452481777911
end

[Ken Chui]

This is the closest I could get:

rename * varx*
gen period = 1989 + _n

reshape long varx, i(period) j(locationtype, string)

gen locs = regexs(0) if(regexm(locationtype, "^[A-Z a-z]*"))
gen isic = regexs(0) if(regexm(locationtype, "[0-9]*$"))
drop locationtype

Results:

. list in 1/10, sep(0)

     +----------------------------------------------+
     | period        varx               locs   isic |
     |----------------------------------------------|
  1. |   1990    3.095669   AustraliaAustria     01 |
  2. |   1990   .57895066   AustraliaAustria    011 |
  3. |   1990   .29464354   AustraliaAustria   0111 |
  4. |   1991   4.5952602   AustraliaAustria     01 |
  5. |   1991   2.2575178   AustraliaAustria    011 |
  6. |   1991   2.1194135   AustraliaAustria   0111 |
  7. |   1992   5.2282254   AustraliaAustria     01 |
  8. |   1992   3.6238991   AustraliaAustria    011 |
  9. |   1992   3.2407176   AustraliaAustria   0111 |
 10. |   1993    3.605282   AustraliaAustria     01 |
     +----------------------------------------------+

The variables locs will need to be split. If there are no "all cap" countries like USA or UK, it's possible to split them by capital letters. Worst case scenario, recode them one by one.

It'd have been helpful if the original data provider can add a symbol like an underscore between country names. Hopefully some guru here can help you with the rest.

[Guest]

Thank you so much Ken for your kind help! I didn't know the function regex. I have countries like NewZealand UnitedStates. I used a code to generate exporter and importer:

gen exporter=regexs(1) if regexm(locs, "((^[A-Z]+)[ ]*([a-z]+))") 
gen importer=subinstr(locs,exporter, "", 1)

It worked for the part when Australia was an exporter though. I have some excel files when NewZealand or UnitedStates is an exporter and didn't import them into Stata yet.