Help reshaping data

[Ama Perera]

Dear Stata community,

I am working with cross-country data, and I'm struggling to reshape the dataset into a usable panel format for analysis.
The dataset is in wide format. Each row represents a unique combination of dimensions such as:

• reportingcountry
• counterpartycountry
• typeofinstruments
• counterpartysector

A data snapshot is as follows:

* Example generated by -dataex-. For more info, type help dataex
clear
input str33 typeofinstruments str81 typeofreportinginstitutions str23 reportingcountry str33 counterpartysector str47 counterpartycountry byte q1977q4 double(q1978q1 q1978q2 q1978q3 q1978q4 q1979q1)
"All instruments"    "All reporting banks/institutions (domestic, foreign, consortium and unclassified)" "Germany"                 "All sectors"                     "Bahrain"                    .   13.183    18.74 -15.385  52.565    -43.698
"All instruments"    "All reporting banks/institutions (domestic, foreign, consortium and unclassified)" "All reporting countries" "Non-bank financial institutions" "Residual developing Europe" .        .        .       .       .          .
"Loans and deposits" "All reporting banks/institutions (domestic, foreign, consortium and unclassified)" "Brazil"                  "Non-banks, total"                "Algeria"                    .        .        .       .       .          .
"All instruments"    "All reporting banks/institutions (domestic, foreign, consortium and unclassified)" "Germany"                 "All sectors"                     "Bulgaria"                   .   68.229   28.654   7.118 -18.038      9.418
"Loans and deposits" "All reporting banks/institutions (domestic, foreign, consortium and unclassified)" "Finland"                 "Non-banks, total"                "Falkland Islands"           .        .        .       .       .          .
"Loans and deposits" "All reporting banks/institutions (domestic, foreign, consortium and unclassified)" "Brazil"                  "Non-banks, total"                "Dominican Republic"         .        .        .       .       .          .
"Loans and deposits" "All reporting banks/institutions (domestic, foreign, consortium and unclassified)" "Brazil"                  "Non-banks, total"                "Denmark"                    .        .        .       .       .          .
"All instruments"    "All reporting banks/institutions (domestic, foreign, consortium and unclassified)" "Germany"                 "All sectors"                     "Burkina Faso"               .        .        .       .       .          .
"All instruments"    "All reporting banks/institutions (domestic, foreign, consortium and unclassified)" "United States"           "Banks, total"                    "All countries (total)"      .        .        .       .       . -10827.233
"All instruments"    "All reporting banks/institutions (domestic, foreign, consortium and unclassified)" "Germany"                 "All sectors"                     "Belgium"                    . -100.835 -314.037  67.059 915.354    -673.92
"Loans and deposits" "All reporting banks/institutions (domestic, foreign, consortium and unclassified)" "Brazil"                  "Non-banks, total"                "Germany"                    .        .        .       .       .          .
"All instruments"    "All reporting banks/institutions (domestic, foreign, consortium and unclassified)" "Germany"                 "All sectors"                     "Brunei"                     .        .        .       1       0          .
"All instruments"    "All reporting banks/institutions (domestic, foreign, consortium and unclassified)" "Germany"                 "All sectors"                     "Bermuda"                    .     -.33   56.153   4.512    .573     26.907
"Loans and deposits" "All reporting banks/institutions (domestic, foreign, consortium and unclassified)" "Brazil"                  "All sectors"                     "South Africa"               .        .        .       .       .          .
"All instruments"    "All reporting banks/institutions (domestic, foreign, consortium and unclassified)" "Germany"                 "All sectors"                     "Benin"                      .        .        .       .       .          .
"All instruments"    "All reporting banks/institutions (domestic, foreign, consortium and unclassified)" "Germany"                 "All sectors"                     "Burundi"                    .        .        .       .    .971       .025
"All instruments"    "All reporting banks/institutions (domestic, foreign, consortium and unclassified)" "Germany"                 "All sectors"                     "Australia"                  .  -26.699   -7.317  10.821 -20.555     -6.524
"All instruments"    "All reporting banks/institutions (domestic, foreign, consortium and unclassified)" "Germany"                 "All sectors"                     "Austria"                    . -141.747  288.487   19.55 411.316    -31.055
"All instruments"    "All reporting banks/institutions (domestic, foreign, consortium and unclassified)" "Germany"                 "All sectors"                     "Argentina"                  .   40.293  106.177    -9.7  36.761     34.098
"All instruments"    "All reporting banks/institutions (domestic, foreign, consortium and unclassified)" "Germany"                 "All sectors"                     "Angola"                     .        .        .       .       .          .
end

I want to reshape this data into long format, where each row gives me:

• A single observation per reportingcountry–counterpartycountry–instrument–po sition–sector–quarter combination,
• With a variable for the value of claims in that quarter,

This will allow me to clearly track the values over time, by sector and instrument, from each reporting country to each counterparty.

Any help would be greatly appreciated.

Thanks!

[Nick Cox]

This may help:

reshape long q, i(typeofinstruments typeofreportinginstitutions reportingcountry counterpartysector counterpartycountry) j(sdate) string 

gen qdate = quarterly(sdate, "YQ")

format qdate %tq

[Ama Perera]

This may help:

reshape long q, i(typeofinstruments typeofreportinginstitutions reportingcountry counterpartysector counterpartycountry) j(sdate) string

gen qdate = quarterly(sdate, "YQ")

format qdate %tq

Thanks Nick. It gives me the error message 'variable id does not uniquely identify the observations'. It does work when I try by keeping only one country. But does not work for the whole dataset.

[Nick Cox]

It works with your data example. So, something is wrong with your full dataset. One possibility is that it came from a spreadsheet with many rows of gunk, such as missing values. Either way, see

help duplicates

and then check (e.g.)

duplicates report typeofinstruments typeofreportinginstitutions reportingcountry counterpartysector counterpartycountry

duplicates list typeofinstruments typeofreportinginstitutions reportingcountry counterpartysector counterpartycountry