Renaming Time variables in my dataset

[Keith Law]

Dear Statalisters

I've a problem in renaming my variables in my dataset. My dataset consists of Credit-to-GDP gaps of 44 different countries around the world from 1999Q1 to 2017Q4. The data example shown below is a simplified version,where:

• BORROWERS_CTY is the abbreviation of a borrowing country
• BORROWERS_CTYName is the name of a borrowing country
• Q1 should stand for Yr 1999, Quarter 1 (or 1999Q1;but somehow when I imported the data from the Excel file, the variable has been named as "Q1")
• Q2 similarly stands for 1999Q2; i.e. all numerical values under this column are Credit-to-GDP gaps of that country in Yr 1999 Q2.
• Q3 for 1999Q3
• Q4 for 1999Q4
• And so on other letters such as G,H, I ...(which represent 2000Q1,2000Q2 and 2000Q3 respectively; They aren't displayed here for simplicity)

* Example generated by -dataex-. To install: ssc install dataex
clear
input str2 BORROWERS_CTY str14 BORROWERS_CTYName str5(Q1 Q2 Q3 Q4)
"RU" "Russia"         ""      ""      ""      ""     
"CO" "Colombia"       ""      ""      ""      ""     
"XM" "Euro area"      ""      ""      ""      ""     
"GB" "United Kingdom" "-0.3"  "1.5"   "1.7"   "3.3"  
"SG" "Singapore"      "6.7"   "6.1"   "3.4"   "2.5"  
"TH" "Thailand"       "4.4"   "-3.2"  "-12.9" "-21.8"
"IT" "Italy"          "0.3"   "3.3"   "3.7"   "6.2"  
"AU" "Australia"      "0.1"   "-0.7"  "0.4"   "2.1"  
"GR" "Greece"         "5.6"   "5.2"   "5.9"   "6.5"  
"NO" "Norway"         "6"     "6.6"   "3"     "1.2"  
"NZ" "New Zealand"    "4.2"   "3.2"   "3.8"   "5"    
"IN" "India"          "-0.5"  "-1.8"  "-1.5"  "-0.2"
"IE" "Ireland"        "17.5"  "24"    "25.4"  "29.6"
"HU" "Hungary"        ""      ""      ""      "7.7"  
"CN" "China"          "6.2"   "6.8"   "7.4"   "8.2"  
"SA" "Saudi Arabia"   ""      ""      ""      ""     
"CA" "Canada"         "0.4"   "0.1"   "-2"    "-5.5"
"CL" "Chile"          "20.9"  "24.3"  "24.5"  "21.5"
"ID" "Indonesia"      "-18.1" "-36.4" "-31.6" "-37.5"
"CH" "Switzerland"    "-9"    "-6.5"  "-5.1"  "-6.7"
"PT" "Portugal"       "23.1"  "27.2"  "28.4"  "30"   
"DE" "Germany"        "3.7"   "4.9"   "5.8"   "7.4"  
"PL" "Poland"         ""      ""      ""      ""     
"IL" "Israel"         ""      ""      ""      ""     
"AR" "Argentina"      "8.8"   "8.8"   "7.7"   "7.1"  
"HK" "Hong Kong SAR"  "-0.5"  "-7.2"  "-8.9"  "-12"  
"TR" "Turkey"         "1.6"   "1"     "0.9"   "0.6"  
"FR" "France"         "-5.5"  "-3.3"  "-2.5"  "-1.1"
"MY" "Malaysia"       "11"    "5.8"   "3.3"   "-5"   
"BR" "Brazil"         ""      ""      ""      ""     
"SE" "Sweden"         "-4"    "-4.5"  "-4.8"  "-1.3"
"NL" "Netherlands"    "6.1"   "6.3"   "7.7"   "8.1"  
"BE" "Belgium"        "14.9"  "15.7"  "17.4"  "17.8"
"CZ" "Czech Republic" ""      ""      ""      ""     
"ES" "Spain"          "12.1"  "17.4"  "17.5"  "19"   
"DK" "Denmark"        "0.9"   "2.1"   "1"     "2.4"  
"JP" "Japan"          "-24.3" "-25.7" "-25.5" "-22.2"
"AT" "Austria"        "-1.3"  "-0.6"  "2.3"   "4.2"  
"ZA" "South Africa"   "7.1"   "6.8"   "5.4"   "4.5"  
"LU" "Luxembourg"     ""      ""      ""      ""     
"KR" "Korea"          "10.4"  "1"     "-3.7"  "-12.8"
"FI" "Finland"        "-21.5" "-18.1" "-18.2" "-18.9"
"MX" "Mexico"         "-10"   "-10.3" "-12"   "-12.3"
"US" "United States"  "1.2"   "1.7"   "3"     "3.5"  
end

------------------ copy up to and including the previous line ------------------

Before reshaping the dataset from wide form to long form, I want to rename the variables beginning horizontally from Q1 till the last time period(say, in this case, Q4), i.e. renaming Q1 as "1999Q1",Q2 as "1999Q2",Q3 as "1999Q3" and so on.

But when I tried the following code,
rename Q1 1999Q1

Stata returned me an error message:

1 new variable name invalid
You attempted to rename Q1 to 1999Q1. That is an invalid
Stata variable name.
r(198);

end of do-file

r(198);

Does anyone have any ideas on how I could rename all of the time variables into the correct names in a faster way? What is wrong with my rename command?

Also, as an additional piece of information in my original dataset, the labels of the variables are right, i.e.label of Q1 is "1999-Q1", that of Q2 is "1999-Q2".

Thank you very much.

Many thanks
Keith

[Andrew Musau]

But when I tried the following code,
rename Q1 1999Q1

It is illegal to name Stata variables as numbers. I guess that "Y1999Q1" could do.

[William Lisowski]

Following up on Andrew's reply, you can rename Q1 through Q4 in a single command following the advice given in the output of help rename group

rename (Q(#)) (Y1999Q(#))

For the remaining years,

rename (G-J) (Y2000Q#), addnumber

and so forth.

[Andrew Musau]

To generalize William's helpful reply so that you do this all at once, you can use the following loop

forvalues i= 1999/2017{
forvalues j=1/4{
local time "`time' Y`i'Q`j'"  
}  
}

rename (C-BZ) (`time')

I assume that the variables run from C-BZ following Excel's convention.

[Keith Law]

It is illegal to name Stata variables as numbers. I guess that "Y1999Q1" could do.

Thank you very much for your help.

[Keith Law]

Following up on Andrew's reply, you can rename Q1 through Q4 in a single command following the advice given in the output of help rename group

rename (Q(#)) (Y1999Q(#))

For the remaining years,

rename (G-J) (Y2000Q#), addnumber

and so forth.

Thank you very much for your help, William.

[Keith Law]

To generalize William's helpful reply so that you do this all at once, you can use the following loop

forvalues i= 1999/2017{
forvalues j=1/4{
local time "`time' Y`i'Q`j'"
}
}

rename (C-BZ) (`time')

I assume that the variables run from C-BZ following Excel's convention.

Thank you very much for your help,Andrew.

Yes, you're right that the years & quarters run from Columns C-BZ.

I sort of understand what your loop means,& correct me if I'm wrong (as I've zero experience in writing a loop). First, it sets a local macro name called "time & it is formatted to be in the form of Year (from 1999-2017,denoted by i) & Quarter(denoted by j).

No offense. As I tried the loop, Stata returned the following error messages:
. rename (C-BZ) (`time')
syntax error
Syntax is
rename oldname newname [, renumber[(#)] addnumber[(#)
> ] sort ...]
rename (oldnames) (newnames) [, renumber[(#)] addnumber[(#)
> ] sort ...]
rename oldnames , {upper|lower|proper}
r(198);

end of do-file

r(198);

It'll be highly appreciated if one do not mind pointing what the problem is. Thank you very much for your help.

[Andrew Musau]

I sort of understand what your loop means,& correct me if I'm wrong (as I've zero experience in writing a loop). First, it sets a local macro name called "time & it is formatted to be in the form of Year (from 1999-2017,denoted by i) & Quarter(denoted by j).

Here you are storing the quarters in a local macro. You can view these as follows

forvalues i= 1999/2017{
forvalues j=1/4{
local time "`time' Y`i'Q`j'"  
}  
}
 display "`time'"

This works for me where I rename variables A-Z as Y1999Q1- Y2011Q2 (13 years, 2 quarters = 26 variables)

set obs 1
*GENERATE VARIABLES A-Z
foreach l in `c(ALPHA)' {
gen `l'=1
}

*STORE NAMES IN MACRO
forvalues i= 1999/2011{
forvalues j=1/2{
local time "`time' Y`i'Q`j'"  
}  
}

*RENAME
 rename (A-Z) (`time')

I suspect that there is something different with your data. Please copy and post the following

ssc install dataex
dataex in 1

[William Lisowski]

Did you perhaps put Andrew's code into the do-file editor, then select and run the loop, and then select and run the rename command?

I believe your problem is that you have written your code in the do-file editor window, and then rather than running everything at once, you are running it by selecting a few lines and running them, then selecting the next few lines and running them, and so on.

If so, consider the following example. In the do-file editor window, I have a two-line program that I run in its entirety.

. do "/var/folders/xr/lm5ccr996k7dspxs35yqzyt80000gp/T//SD30770.000000"

. local message Hello, world.

. display "`message'"
Hello, world.

.
end of do-file

Now I run the same two lines by selecting the first line and running it, then selecting the second line and running it.

. do "/var/folders/xr/lm5ccr996k7dspxs35yqzyt80000gp/T//SD30770.000000"

. local message Hello, world.

.
end of do-file

. do "/var/folders/xr/lm5ccr996k7dspxs35yqzyt80000gp/T//SD30770.000000"

. display "`message'"


.
end of do-file

The important thing to keep in mind is that local macros vanish when the do-file within which they were created ends. If you look carefully at the results above, you'll see that when I selected a single line to run, it was copied into a temporary do-file and run, so even though both lines are in the same window in the do-file editor, they are run as separate do-files, and local macro defined in the first line vanishes at the end of that do-file, and is undefined when the second line is run.

So when you selected and ran to rename command, the local macro time was undefined, so Stata saw

. rename (C-BZ) ()
syntax error
    Syntax is
        rename  oldname    newname   [, renumber[(#)] addnumber[(#)] sort ...]
        rename (oldnames) (newnames) [, renumber[(#)] addnumber[(#)] sort ...]
        rename  oldnames              , {upper|lower|proper}
r(198);

That's my guess about what happened.

Your understanding of the loop is correct - it builds the list of variable names that you want to rename your variables to, as the code below demonstrates, and as a side benefit, also demonstrates that the local macro does indeed vanish after the do-file ends.

. do "/var/folders/xr/lm5ccr996k7dspxs35yqzyt80000gp/T//SD42083.000000"

. forvalues i= 1999/2017{
  2. forvalues j=1/4{
  3. local time "`time' Y`i'Q`j'"  
  4. }  
  5. }

. 
. macro list _time
_time:           Y1999Q1 Y1999Q2 Y1999Q3 Y1999Q4 Y2000Q1 Y2000Q2 Y2000Q3 Y2000Q4 Y2001Q1 Y2001Q2
                Y2001Q3 Y2001Q4 Y2002Q1 Y2002Q2 Y2002Q3 Y2002Q4 Y2003Q1 Y2003Q2 Y2003Q3 Y2003Q4
                Y2004Q1 Y2004Q2 Y2004Q3 Y2004Q4 Y2005Q1 Y2005Q2 Y2005Q3 Y2005Q4 Y2006Q1 Y2006Q2
                Y2006Q3 Y2006Q4 Y2007Q1 Y2007Q2 Y2007Q3 Y2007Q4 Y2008Q1 Y2008Q2 Y2008Q3 Y2008Q4
                Y2009Q1 Y2009Q2 Y2009Q3 Y2009Q4 Y2010Q1 Y2010Q2 Y2010Q3 Y2010Q4 Y2011Q1 Y2011Q2
                Y2011Q3 Y2011Q4 Y2012Q1 Y2012Q2 Y2012Q3 Y2012Q4 Y2013Q1 Y2013Q2 Y2013Q3 Y2013Q4
                Y2014Q1 Y2014Q2 Y2014Q3 Y2014Q4 Y2015Q1 Y2015Q2 Y2015Q3 Y2015Q4 Y2016Q1 Y2016Q2
                Y2016Q3 Y2016Q4 Y2017Q1 Y2017Q2 Y2017Q3 Y2017Q4

. 
end of do-file

. macro list _time
local macro `time' not found
r(111);

[Nick Cox]

Renaming is just the immediate problem. You shouldn't want to keep this data layout in any case. Before you can do anything useful with these data in Stata, you need a long layout and for the numeric values to be in numeric variables.

No loops or local macros needed either.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str2 BORROWERS_CTY str14 BORROWERS_CTYName str5(Q1 Q2 Q3 Q4)
"RU" "Russia"         ""      ""      ""      ""     
"CO" "Colombia"       ""      ""      ""      ""     
"XM" "Euro area"      ""      ""      ""      ""     
"GB" "United Kingdom" "-0.3"  "1.5"   "1.7"   "3.3"  
"SG" "Singapore"      "6.7"   "6.1"   "3.4"   "2.5"  
"TH" "Thailand"       "4.4"   "-3.2"  "-12.9" "-21.8"
"IT" "Italy"          "0.3"   "3.3"   "3.7"   "6.2"  
"AU" "Australia"      "0.1"   "-0.7"  "0.4"   "2.1"  
"GR" "Greece"         "5.6"   "5.2"   "5.9"   "6.5"  
"NO" "Norway"         "6"     "6.6"   "3"     "1.2"  
"NZ" "New Zealand"    "4.2"   "3.2"   "3.8"   "5"    
"IN" "India"          "-0.5"  "-1.8"  "-1.5"  "-0.2"
"IE" "Ireland"        "17.5"  "24"    "25.4"  "29.6"
"HU" "Hungary"        ""      ""      ""      "7.7"  
"CN" "China"          "6.2"   "6.8"   "7.4"   "8.2"  
"SA" "Saudi Arabia"   ""      ""      ""      ""     
"CA" "Canada"         "0.4"   "0.1"   "-2"    "-5.5"
"CL" "Chile"          "20.9"  "24.3"  "24.5"  "21.5"
"ID" "Indonesia"      "-18.1" "-36.4" "-31.6" "-37.5"
"CH" "Switzerland"    "-9"    "-6.5"  "-5.1"  "-6.7"
"PT" "Portugal"       "23.1"  "27.2"  "28.4"  "30"   
"DE" "Germany"        "3.7"   "4.9"   "5.8"   "7.4"  
"PL" "Poland"         ""      ""      ""      ""     
"IL" "Israel"         ""      ""      ""      ""     
"AR" "Argentina"      "8.8"   "8.8"   "7.7"   "7.1"  
"HK" "Hong Kong SAR"  "-0.5"  "-7.2"  "-8.9"  "-12"  
"TR" "Turkey"         "1.6"   "1"     "0.9"   "0.6"  
"FR" "France"         "-5.5"  "-3.3"  "-2.5"  "-1.1"
"MY" "Malaysia"       "11"    "5.8"   "3.3"   "-5"   
"BR" "Brazil"         ""      ""      ""      ""     
"SE" "Sweden"         "-4"    "-4.5"  "-4.8"  "-1.3"
"NL" "Netherlands"    "6.1"   "6.3"   "7.7"   "8.1"  
"BE" "Belgium"        "14.9"  "15.7"  "17.4"  "17.8"
"CZ" "Czech Republic" ""      ""      ""      ""     
"ES" "Spain"          "12.1"  "17.4"  "17.5"  "19"   
"DK" "Denmark"        "0.9"   "2.1"   "1"     "2.4"  
"JP" "Japan"          "-24.3" "-25.7" "-25.5" "-22.2"
"AT" "Austria"        "-1.3"  "-0.6"  "2.3"   "4.2"  
"ZA" "South Africa"   "7.1"   "6.8"   "5.4"   "4.5"  
"LU" "Luxembourg"     ""      ""      ""      ""     
"KR" "Korea"          "10.4"  "1"     "-3.7"  "-12.8"
"FI" "Finland"        "-21.5" "-18.1" "-18.2" "-18.9"
"MX" "Mexico"         "-10"   "-10.3" "-12"   "-12.3"
"US" "United States"  "1.2"   "1.7"   "3"     "3.5"  
end

gen G = "42" 
gen H = "666" 

rename (Q1-Q4 G H) (Q#), addnumber 

reshape long Q, i(BORROWERS_CTY) j(qdate)

destring Q, replace 
replace qdate = qdate + yq(1998, 4) 
format qdate  %tq 

list in 1/24, sepby(BORROWERS_CTY) 

     +--------------------------------------+
     | BORROW~Y    qdate   BORROWE~e      Q |
     |--------------------------------------|
  1. |       AR   1999q1   Argentina    8.8 |
  2. |       AR   1999q2   Argentina    8.8 |
  3. |       AR   1999q3   Argentina    7.7 |
  4. |       AR   1999q4   Argentina    7.1 |
  5. |       AR   2000q1   Argentina     42 |
  6. |       AR   2000q2   Argentina    666 |
     |--------------------------------------|
  7. |       AT   1999q1     Austria   -1.3 |
  8. |       AT   1999q2     Austria    -.6 |
  9. |       AT   1999q3     Austria    2.3 |
 10. |       AT   1999q4     Austria    4.2 |
 11. |       AT   2000q1     Austria     42 |
 12. |       AT   2000q2     Austria    666 |
     |--------------------------------------|
 13. |       AU   1999q1   Australia     .1 |
 14. |       AU   1999q2   Australia    -.7 |
 15. |       AU   1999q3   Australia     .4 |
 16. |       AU   1999q4   Australia    2.1 |
 17. |       AU   2000q1   Australia     42 |
 18. |       AU   2000q2   Australia    666 |
     |--------------------------------------|
 19. |       BE   1999q1     Belgium   14.9 |
 20. |       BE   1999q2     Belgium   15.7 |
 21. |       BE   1999q3     Belgium   17.4 |
 22. |       BE   1999q4     Belgium   17.8 |
 23. |       BE   2000q1     Belgium     42 |
 24. |       BE   2000q2     Belgium    666 |
     +--------------------------------------+

[Keith Law]

Here you are storing the quarters in a local macro. You can view these as follows

forvalues i= 1999/2017{
forvalues j=1/4{
local time "`time' Y`i'Q`j'"
}
}
display "`time'"

This works for me where I rename variables A-Z as Y1999Q1- Y2011Q2 (13 years, 2 quarters = 26 variables)

set obs 1
*GENERATE VARIABLES A-Z
foreach l in `c(ALPHA)' {
gen `l'=1
}

*STORE NAMES IN MACRO
forvalues i= 1999/2011{
forvalues j=1/2{
local time "`time' Y`i'Q`j'"
}
}

*RENAME
rename (A-Z) (`time')

I suspect that there is something different with your data. Please copy and post the following

ssc install dataex
dataex in 1

It works after running all the lines at once,like what William has said.It was indeed because I ran the codes line by line in a do-file that has caused the local macro to vanish. This problem disappears after selecting all the lines till the line staring with "rename" & run them all at once.

It also works for the new example that you have given(the one that generates alphabetical letters & renaming them as time in Yr & quarters) after I ran all the lines at once.

The data may be having some problems as well,like what you've said. I downloaded it from Bank of International Settlement(BIS) website in Excel format (as attached if you're interested).Since the first 24 rows describe the details of the variables(e.g. unit of measurement,frequency) & the dataset, I imported starting from row 26 till the last observation.


Thank you very much for your help, Andrew.

[Keith Law]

Did you perhaps put Andrew's code into the do-file editor, then select and run the loop, and then select and run the rename command?

I believe your problem is that you have written your code in the do-file editor window, and then rather than running everything at once, you are running it by selecting a few lines and running them, then selecting the next few lines and running them, and so on.

If so, consider the following example. In the do-file editor window, I have a two-line program that I run in its entirety.

. do "/var/folders/xr/lm5ccr996k7dspxs35yqzyt80000gp/T//SD30770.000000"

. local message Hello, world.

. display "`message'"
Hello, world.

.
end of do-file

Now I run the same two lines by selecting the first line and running it, then selecting the second line and running it.

. do "/var/folders/xr/lm5ccr996k7dspxs35yqzyt80000gp/T//SD30770.000000"

. local message Hello, world.

.
end of do-file

. do "/var/folders/xr/lm5ccr996k7dspxs35yqzyt80000gp/T//SD30770.000000"

. display "`message'"


.
end of do-file

The important thing to keep in mind is that local macros vanish when the do-file within which they were created ends. If you look carefully at the results above, you'll see that when I selected a single line to run, it was copied into a temporary do-file and run, so even though both lines are in the same window in the do-file editor, they are run as separate do-files, and local macro defined in the first line vanishes at the end of that do-file, and is undefined when the second line is run.

So when you selected and ran to rename command, the local macro time was undefined, so Stata saw

. rename (C-BZ) ()
syntax error
Syntax is
rename oldname newname [, renumber[(#)] addnumber[(#)] sort ...]
rename (oldnames) (newnames) [, renumber[(#)] addnumber[(#)] sort ...]
rename oldnames , {upper|lower|proper}
r(198);

That's my guess about what happened.

Your understanding of the loop is correct - it builds the list of variable names that you want to rename your variables to, as the code below demonstrates, and as a side benefit, also demonstrates that the local macro does indeed vanish after the do-file ends.

. do "/var/folders/xr/lm5ccr996k7dspxs35yqzyt80000gp/T//SD42083.000000"

. forvalues i= 1999/2017{
2. forvalues j=1/4{
3. local time "`time' Y`i'Q`j'"
4. }
5. }

.
. macro list _time
_time: Y1999Q1 Y1999Q2 Y1999Q3 Y1999Q4 Y2000Q1 Y2000Q2 Y2000Q3 Y2000Q4 Y2001Q1 Y2001Q2
Y2001Q3 Y2001Q4 Y2002Q1 Y2002Q2 Y2002Q3 Y2002Q4 Y2003Q1 Y2003Q2 Y2003Q3 Y2003Q4
Y2004Q1 Y2004Q2 Y2004Q3 Y2004Q4 Y2005Q1 Y2005Q2 Y2005Q3 Y2005Q4 Y2006Q1 Y2006Q2
Y2006Q3 Y2006Q4 Y2007Q1 Y2007Q2 Y2007Q3 Y2007Q4 Y2008Q1 Y2008Q2 Y2008Q3 Y2008Q4
Y2009Q1 Y2009Q2 Y2009Q3 Y2009Q4 Y2010Q1 Y2010Q2 Y2010Q3 Y2010Q4 Y2011Q1 Y2011Q2
Y2011Q3 Y2011Q4 Y2012Q1 Y2012Q2 Y2012Q3 Y2012Q4 Y2013Q1 Y2013Q2 Y2013Q3 Y2013Q4
Y2014Q1 Y2014Q2 Y2014Q3 Y2014Q4 Y2015Q1 Y2015Q2 Y2015Q3 Y2015Q4 Y2016Q1 Y2016Q2
Y2016Q3 Y2016Q4 Y2017Q1 Y2017Q2 Y2017Q3 Y2017Q4

.
end of do-file

. macro list _time
local macro `time' not found
r(111);

Yes,it was really because I ran the selected lines of loop first & then the rename command line that has resulted in the problem. After running the line beginning with local & that with display in your example, the stata output shows the message "Hello, World" instead of just the code display "`message'" & nothing else.Thank you very much for letting me know about this,William.

[Keith Law]

Renaming is just the immediate problem. You shouldn't want to keep this data layout in any case. Before you can do anything useful with these data in Stata, you need a long layout and for the numeric values to be in numeric variables.

No loops or local macros needed either.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str2 BORROWERS_CTY str14 BORROWERS_CTYName str5(Q1 Q2 Q3 Q4)
"RU" "Russia" "" "" "" ""
"CO" "Colombia" "" "" "" ""
"XM" "Euro area" "" "" "" ""
"GB" "United Kingdom" "-0.3" "1.5" "1.7" "3.3"
"SG" "Singapore" "6.7" "6.1" "3.4" "2.5"
"TH" "Thailand" "4.4" "-3.2" "-12.9" "-21.8"
"IT" "Italy" "0.3" "3.3" "3.7" "6.2"
"AU" "Australia" "0.1" "-0.7" "0.4" "2.1"
"GR" "Greece" "5.6" "5.2" "5.9" "6.5"
"NO" "Norway" "6" "6.6" "3" "1.2"
"NZ" "New Zealand" "4.2" "3.2" "3.8" "5"
"IN" "India" "-0.5" "-1.8" "-1.5" "-0.2"
"IE" "Ireland" "17.5" "24" "25.4" "29.6"
"HU" "Hungary" "" "" "" "7.7"
"CN" "China" "6.2" "6.8" "7.4" "8.2"
"SA" "Saudi Arabia" "" "" "" ""
"CA" "Canada" "0.4" "0.1" "-2" "-5.5"
"CL" "Chile" "20.9" "24.3" "24.5" "21.5"
"ID" "Indonesia" "-18.1" "-36.4" "-31.6" "-37.5"
"CH" "Switzerland" "-9" "-6.5" "-5.1" "-6.7"
"PT" "Portugal" "23.1" "27.2" "28.4" "30"
"DE" "Germany" "3.7" "4.9" "5.8" "7.4"
"PL" "Poland" "" "" "" ""
"IL" "Israel" "" "" "" ""
"AR" "Argentina" "8.8" "8.8" "7.7" "7.1"
"HK" "Hong Kong SAR" "-0.5" "-7.2" "-8.9" "-12"
"TR" "Turkey" "1.6" "1" "0.9" "0.6"
"FR" "France" "-5.5" "-3.3" "-2.5" "-1.1"
"MY" "Malaysia" "11" "5.8" "3.3" "-5"
"BR" "Brazil" "" "" "" ""
"SE" "Sweden" "-4" "-4.5" "-4.8" "-1.3"
"NL" "Netherlands" "6.1" "6.3" "7.7" "8.1"
"BE" "Belgium" "14.9" "15.7" "17.4" "17.8"
"CZ" "Czech Republic" "" "" "" ""
"ES" "Spain" "12.1" "17.4" "17.5" "19"
"DK" "Denmark" "0.9" "2.1" "1" "2.4"
"JP" "Japan" "-24.3" "-25.7" "-25.5" "-22.2"
"AT" "Austria" "-1.3" "-0.6" "2.3" "4.2"
"ZA" "South Africa" "7.1" "6.8" "5.4" "4.5"
"LU" "Luxembourg" "" "" "" ""
"KR" "Korea" "10.4" "1" "-3.7" "-12.8"
"FI" "Finland" "-21.5" "-18.1" "-18.2" "-18.9"
"MX" "Mexico" "-10" "-10.3" "-12" "-12.3"
"US" "United States" "1.2" "1.7" "3" "3.5"
end

gen G = "42"
gen H = "666"

rename (Q1-Q4 G H) (Q#), addnumber

reshape long Q, i(BORROWERS_CTY) j(qdate)

destring Q, replace
replace qdate = qdate + yq(1998, 4)
format qdate %tq

list in 1/24, sepby(BORROWERS_CTY)

+--------------------------------------+
| BORROW~Y qdate BORROWE~e Q |
|--------------------------------------|
1. | AR 1999q1 Argentina 8.8 |
2. | AR 1999q2 Argentina 8.8 |
3. | AR 1999q3 Argentina 7.7 |
4. | AR 1999q4 Argentina 7.1 |
5. | AR 2000q1 Argentina 42 |
6. | AR 2000q2 Argentina 666 |
|--------------------------------------|
7. | AT 1999q1 Austria -1.3 |
8. | AT 1999q2 Austria -.6 |
9. | AT 1999q3 Austria 2.3 |
10. | AT 1999q4 Austria 4.2 |
11. | AT 2000q1 Austria 42 |
12. | AT 2000q2 Austria 666 |
|--------------------------------------|
13. | AU 1999q1 Australia .1 |
14. | AU 1999q2 Australia -.7 |
15. | AU 1999q3 Australia .4 |
16. | AU 1999q4 Australia 2.1 |
17. | AU 2000q1 Australia 42 |
18. | AU 2000q2 Australia 666 |
|--------------------------------------|
19. | BE 1999q1 Belgium 14.9 |
20. | BE 1999q2 Belgium 15.7 |
21. | BE 1999q3 Belgium 17.4 |
22. | BE 1999q4 Belgium 17.8 |
23. | BE 2000q1 Belgium 42 |
24. | BE 2000q2 Belgium 666 |
+--------------------------------------+

Thank you very much for your help,Nick. Indeed, I need to convert the data from wide to long form & destring the dates to be numeric variables before conducting any analysis.

This is very helpful.