Announcement

Collapse
No announcement yet.
X
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts
  • #1

    Reshaping Panel Data with Quarterly Data.

    Hi, a relatively novice Stata user here, I've encountered problems with the reshape command. Specifically creating quarterly observations as a j variable in long format.

    Here's an example of my dataset.

    input int s_id byte c_id str23 filename str83 code indicator float(yr2014q1 yr2014q2)
    1 1 "PSIFIs_IBD_Bahrain" "AD06 - Accommodation And Food Service Activities" 0 0
    1 2 "PSIFIs_IBD_Brunei" "AD06 - Accommodation And Food Service Activities" 33.87 33.74
    1 3 "PSIFIs_IBD_Indonesia" "AD06 - Accommodation And Food Service Activities" 60.58 70.81
    1 4 "PSIFIs_B_Jordan" "AD06 - Accommodation And Food Service Activities" 6.91 3.39
    1 6 "PSIFIs_IBD_Nigeria" "AD06 - Accommodation And Food Service Activities" 0 0
    1 7 "PSIFIs_IBD_Oman" "AD06 - Accommodation And Food Service Activities" 0 0
    1 8 "PSIFIs_IBD_Pakistan" "AD06 - Accommodation And Food Service Activities" 2.13 2.17
    1 9 "PSIFIs_IBD_Saudi Arabia" "AD06 - Accommodation And Food Service Activities" 175.91 170.01
    1 10 "PSIFIs_IBD_Sudan" "AD06 - Accommodation And Food Service Activities" 0 0
    1 11 "PSIFIs_IBD_TUR" "AD06 - Accommodation And Food Service Activities" 0 0
    1 12 "PSIFIs_IBD_UAE" "AD06 - Accommodation And Food Service Activities" 323.46 368.62
    2 1 "PSIFIs_IBD_Bahrain" "AD06 - Activities Of Extraterritorial Organisations And Bodies" 0 0
    2 2 "PSIFIs_IBD_Brunei" "AD06 - Activities Of Extraterritorial Organisations And Bodies" 0 0
    2 3 "PSIFIs_IBD_Indonesia" "AD06 - Activities Of Extraterritorial Organisations And Bodies" 0 .06
    2 4 "PSIFIs_B_Jordan" "AD06 - Activities Of Extraterritorial Organisations And Bodies" 0 0
    2 6 "PSIFIs_IBD_Nigeria" "AD06 - Activities Of Extraterritorial Organisations And Bodies" 0 0
    2 7 "PSIFIs_IBD_Oman" "AD06 - Activities Of Extraterritorial Organisations And Bodies" 0

    My ID variables include a country code (c_id) as well as a code for each variable (s_id).

    The following problems I've encountered:

    1. Do I declare my time variable as quarterly before using the command:

    "reshape long yr, i[c_id s_id] j[year]"
    I initially used year as it is a time variable that stata could recognise hoping to be able to declare the variables as quarterly after the reshaping, however,

    2. Error "option i incorrectly specified" occurred

    I also tried to follow a StataCorp video on YouTube, however, their data had stub names with each observation, following their advice the following error occurred:

    no xij variables found
    You typed something like reshape wide a b, i(i) j(j).
    reshape looked for existing variables named a# and b# but could not find any. 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 |
    +---------------+

    long to wide: reshape wide a b, i(i) j(j) (j existing variable)
    wide to long: reshape long a b, i(i) j(j) (j new variable)
    r(111);

    Any advice would be appreciated.

    Thank you!

    Tags: None
  • #2
    Code:
    * Example generated by -dataex-. For more info, type help dataex
clear
input int s_id byte c_id str83(filename code) float(yr2014q1 yr2014q2)
1  1 "PSIFIs_IBD_Bahrain"      "AD06 - Accommodation And Food Service Activities"                    0      0
1  2 "PSIFIs_IBD_Brunei"       "AD06 - Accommodation And Food Service Activities"                33.87  33.74
1  3 "PSIFIs_IBD_Indonesia"    "AD06 - Accommodation And Food Service Activities"                60.58  70.81
1  4 "PSIFIs_B_Jordan"         "AD06 - Accommodation And Food Service Activities"                 6.91   3.39
1  6 "PSIFIs_IBD_Nigeria"      "AD06 - Accommodation And Food Service Activities"                    0      0
1  7 "PSIFIs_IBD_Oman"         "AD06 - Accommodation And Food Service Activities"                    0      0
1  8 "PSIFIs_IBD_Pakistan"     "AD06 - Accommodation And Food Service Activities"                 2.13   2.17
1  9 "PSIFIs_IBD_Saudi Arabia" "AD06 - Accommodation And Food Service Activities"               175.91 170.01
1 10 "PSIFIs_IBD_Sudan"        "AD06 - Accommodation And Food Service Activities"                    0      0
1 11 "PSIFIs_IBD_TUR"          "AD06 - Accommodation And Food Service Activities"                    0      0
1 12 "PSIFIs_IBD_UAE"          "AD06 - Accommodation And Food Service Activities"               323.46 368.62
2  1 "PSIFIs_IBD_Bahrain"      "AD06 - Activities Of Extraterritorial Organisations And Bodies"      0      0
2  2 "PSIFIs_IBD_Brunei"       "AD06 - Activities Of Extraterritorial Organisations And Bodies"      0      0
2  3 "PSIFIs_IBD_Indonesia"    "AD06 - Activities Of Extraterritorial Organisations And Bodies"      0    .06
2  4 "PSIFIs_B_Jordan"         "AD06 - Activities Of Extraterritorial Organisations And Bodies"      0      0
2  6 "PSIFIs_IBD_Nigeria"      "AD06 - Activities Of Extraterritorial Organisations And Bodies"      0      0
end

rename yr* value*
reshape long value, i(c_id s_id) j(qtr) string
gen quarter= quarterly(qtr, "yq"), after(qtr)
format quarter %tq
    Res.:

    Code:
    . l, sep(0)

     +------------------------------------------------------------------------------------------------------------------------------------+
     | c_id   s_id      qtr   quarter                  filename                                                             code    value |
     |------------------------------------------------------------------------------------------------------------------------------------|
  1. |    1      1   2014q1    2014q1        PSIFIs_IBD_Bahrain                 AD06 - Accommodation And Food Service Activities        0 |
  2. |    1      1   2014q2    2014q2        PSIFIs_IBD_Bahrain                 AD06 - Accommodation And Food Service Activities        0 |
  3. |    1      2   2014q1    2014q1        PSIFIs_IBD_Bahrain   AD06 - Activities Of Extraterritorial Organisations And Bodies        0 |
  4. |    1      2   2014q2    2014q2        PSIFIs_IBD_Bahrain   AD06 - Activities Of Extraterritorial Organisations And Bodies        0 |
  5. |    2      1   2014q1    2014q1         PSIFIs_IBD_Brunei                 AD06 - Accommodation And Food Service Activities    33.87 |
  6. |    2      1   2014q2    2014q2         PSIFIs_IBD_Brunei                 AD06 - Accommodation And Food Service Activities    33.74 |
  7. |    2      2   2014q1    2014q1         PSIFIs_IBD_Brunei   AD06 - Activities Of Extraterritorial Organisations And Bodies        0 |
  8. |    2      2   2014q2    2014q2         PSIFIs_IBD_Brunei   AD06 - Activities Of Extraterritorial Organisations And Bodies        0 |
  9. |    3      1   2014q1    2014q1      PSIFIs_IBD_Indonesia                 AD06 - Accommodation And Food Service Activities    60.58 |
 10. |    3      1   2014q2    2014q2      PSIFIs_IBD_Indonesia                 AD06 - Accommodation And Food Service Activities    70.81 |
 11. |    3      2   2014q1    2014q1      PSIFIs_IBD_Indonesia   AD06 - Activities Of Extraterritorial Organisations And Bodies        0 |
 12. |    3      2   2014q2    2014q2      PSIFIs_IBD_Indonesia   AD06 - Activities Of Extraterritorial Organisations And Bodies      .06 |
 13. |    4      1   2014q1    2014q1           PSIFIs_B_Jordan                 AD06 - Accommodation And Food Service Activities     6.91 |
 14. |    4      1   2014q2    2014q2           PSIFIs_B_Jordan                 AD06 - Accommodation And Food Service Activities     3.39 |
 15. |    4      2   2014q1    2014q1           PSIFIs_B_Jordan   AD06 - Activities Of Extraterritorial Organisations And Bodies        0 |
 16. |    4      2   2014q2    2014q2           PSIFIs_B_Jordan   AD06 - Activities Of Extraterritorial Organisations And Bodies        0 |
 17. |    6      1   2014q1    2014q1        PSIFIs_IBD_Nigeria                 AD06 - Accommodation And Food Service Activities        0 |
 18. |    6      1   2014q2    2014q2        PSIFIs_IBD_Nigeria                 AD06 - Accommodation And Food Service Activities        0 |
 19. |    6      2   2014q1    2014q1        PSIFIs_IBD_Nigeria   AD06 - Activities Of Extraterritorial Organisations And Bodies        0 |
 20. |    6      2   2014q2    2014q2        PSIFIs_IBD_Nigeria   AD06 - Activities Of Extraterritorial Organisations And Bodies        0 |
 21. |    7      1   2014q1    2014q1           PSIFIs_IBD_Oman                 AD06 - Accommodation And Food Service Activities        0 |
 22. |    7      1   2014q2    2014q2           PSIFIs_IBD_Oman                 AD06 - Accommodation And Food Service Activities        0 |
 23. |    8      1   2014q1    2014q1       PSIFIs_IBD_Pakistan                 AD06 - Accommodation And Food Service Activities     2.13 |
 24. |    8      1   2014q2    2014q2       PSIFIs_IBD_Pakistan                 AD06 - Accommodation And Food Service Activities     2.17 |
 25. |    9      1   2014q1    2014q1   PSIFIs_IBD_Saudi Arabia                 AD06 - Accommodation And Food Service Activities   175.91 |
 26. |    9      1   2014q2    2014q2   PSIFIs_IBD_Saudi Arabia                 AD06 - Accommodation And Food Service Activities   170.01 |
 27. |   10      1   2014q1    2014q1          PSIFIs_IBD_Sudan                 AD06 - Accommodation And Food Service Activities        0 |
 28. |   10      1   2014q2    2014q2          PSIFIs_IBD_Sudan                 AD06 - Accommodation And Food Service Activities        0 |
 29. |   11      1   2014q1    2014q1            PSIFIs_IBD_TUR                 AD06 - Accommodation And Food Service Activities        0 |
 30. |   11      1   2014q2    2014q2            PSIFIs_IBD_TUR                 AD06 - Accommodation And Food Service Activities        0 |
 31. |   12      1   2014q1    2014q1            PSIFIs_IBD_UAE                 AD06 - Accommodation And Food Service Activities   323.46 |
 32. |   12      1   2014q2    2014q2            PSIFIs_IBD_UAE                 AD06 - Accommodation And Food Service Activities   368.62 |
     +------------------------------------------------------------------------------------------------------------------------------------+

.
    • 1 like

    Comment

    • #3
      Originally posted by Andrew Musau View Post
      Code:
      * Example generated by -dataex-. For more info, type help dataex
clear
input int s_id byte c_id str83(filename code) float(yr2014q1 yr2014q2)
1 1 "PSIFIs_IBD_Bahrain" "AD06 - Accommodation And Food Service Activities" 0 0
1 2 "PSIFIs_IBD_Brunei" "AD06 - Accommodation And Food Service Activities" 33.87 33.74
1 3 "PSIFIs_IBD_Indonesia" "AD06 - Accommodation And Food Service Activities" 60.58 70.81
1 4 "PSIFIs_B_Jordan" "AD06 - Accommodation And Food Service Activities" 6.91 3.39
1 6 "PSIFIs_IBD_Nigeria" "AD06 - Accommodation And Food Service Activities" 0 0
1 7 "PSIFIs_IBD_Oman" "AD06 - Accommodation And Food Service Activities" 0 0
1 8 "PSIFIs_IBD_Pakistan" "AD06 - Accommodation And Food Service Activities" 2.13 2.17
1 9 "PSIFIs_IBD_Saudi Arabia" "AD06 - Accommodation And Food Service Activities" 175.91 170.01
1 10 "PSIFIs_IBD_Sudan" "AD06 - Accommodation And Food Service Activities" 0 0
1 11 "PSIFIs_IBD_TUR" "AD06 - Accommodation And Food Service Activities" 0 0
1 12 "PSIFIs_IBD_UAE" "AD06 - Accommodation And Food Service Activities" 323.46 368.62
2 1 "PSIFIs_IBD_Bahrain" "AD06 - Activities Of Extraterritorial Organisations And Bodies" 0 0
2 2 "PSIFIs_IBD_Brunei" "AD06 - Activities Of Extraterritorial Organisations And Bodies" 0 0
2 3 "PSIFIs_IBD_Indonesia" "AD06 - Activities Of Extraterritorial Organisations And Bodies" 0 .06
2 4 "PSIFIs_B_Jordan" "AD06 - Activities Of Extraterritorial Organisations And Bodies" 0 0
2 6 "PSIFIs_IBD_Nigeria" "AD06 - Activities Of Extraterritorial Organisations And Bodies" 0 0
end

rename yr* value*
reshape long value, i(c_id s_id) j(qtr) string
gen quarter= quarterly(qtr, "yq"), after(qtr)
format quarter %tq
      Res.:

      Code:
      . l, sep(0)

+------------------------------------------------------------------------------------------------------------------------------------+
| c_id s_id qtr quarter filename code value |
|------------------------------------------------------------------------------------------------------------------------------------|
1. | 1 1 2014q1 2014q1 PSIFIs_IBD_Bahrain AD06 - Accommodation And Food Service Activities 0 |
2. | 1 1 2014q2 2014q2 PSIFIs_IBD_Bahrain AD06 - Accommodation And Food Service Activities 0 |
3. | 1 2 2014q1 2014q1 PSIFIs_IBD_Bahrain AD06 - Activities Of Extraterritorial Organisations And Bodies 0 |
4. | 1 2 2014q2 2014q2 PSIFIs_IBD_Bahrain AD06 - Activities Of Extraterritorial Organisations And Bodies 0 |
5. | 2 1 2014q1 2014q1 PSIFIs_IBD_Brunei AD06 - Accommodation And Food Service Activities 33.87 |
6. | 2 1 2014q2 2014q2 PSIFIs_IBD_Brunei AD06 - Accommodation And Food Service Activities 33.74 |
7. | 2 2 2014q1 2014q1 PSIFIs_IBD_Brunei AD06 - Activities Of Extraterritorial Organisations And Bodies 0 |
8. | 2 2 2014q2 2014q2 PSIFIs_IBD_Brunei AD06 - Activities Of Extraterritorial Organisations And Bodies 0 |
9. | 3 1 2014q1 2014q1 PSIFIs_IBD_Indonesia AD06 - Accommodation And Food Service Activities 60.58 |
10. | 3 1 2014q2 2014q2 PSIFIs_IBD_Indonesia AD06 - Accommodation And Food Service Activities 70.81 |
11. | 3 2 2014q1 2014q1 PSIFIs_IBD_Indonesia AD06 - Activities Of Extraterritorial Organisations And Bodies 0 |
12. | 3 2 2014q2 2014q2 PSIFIs_IBD_Indonesia AD06 - Activities Of Extraterritorial Organisations And Bodies .06 |
13. | 4 1 2014q1 2014q1 PSIFIs_B_Jordan AD06 - Accommodation And Food Service Activities 6.91 |
14. | 4 1 2014q2 2014q2 PSIFIs_B_Jordan AD06 - Accommodation And Food Service Activities 3.39 |
15. | 4 2 2014q1 2014q1 PSIFIs_B_Jordan AD06 - Activities Of Extraterritorial Organisations And Bodies 0 |
16. | 4 2 2014q2 2014q2 PSIFIs_B_Jordan AD06 - Activities Of Extraterritorial Organisations And Bodies 0 |
17. | 6 1 2014q1 2014q1 PSIFIs_IBD_Nigeria AD06 - Accommodation And Food Service Activities 0 |
18. | 6 1 2014q2 2014q2 PSIFIs_IBD_Nigeria AD06 - Accommodation And Food Service Activities 0 |
19. | 6 2 2014q1 2014q1 PSIFIs_IBD_Nigeria AD06 - Activities Of Extraterritorial Organisations And Bodies 0 |
20. | 6 2 2014q2 2014q2 PSIFIs_IBD_Nigeria AD06 - Activities Of Extraterritorial Organisations And Bodies 0 |
21. | 7 1 2014q1 2014q1 PSIFIs_IBD_Oman AD06 - Accommodation And Food Service Activities 0 |
22. | 7 1 2014q2 2014q2 PSIFIs_IBD_Oman AD06 - Accommodation And Food Service Activities 0 |
23. | 8 1 2014q1 2014q1 PSIFIs_IBD_Pakistan AD06 - Accommodation And Food Service Activities 2.13 |
24. | 8 1 2014q2 2014q2 PSIFIs_IBD_Pakistan AD06 - Accommodation And Food Service Activities 2.17 |
25. | 9 1 2014q1 2014q1 PSIFIs_IBD_Saudi Arabia AD06 - Accommodation And Food Service Activities 175.91 |
26. | 9 1 2014q2 2014q2 PSIFIs_IBD_Saudi Arabia AD06 - Accommodation And Food Service Activities 170.01 |
27. | 10 1 2014q1 2014q1 PSIFIs_IBD_Sudan AD06 - Accommodation And Food Service Activities 0 |
28. | 10 1 2014q2 2014q2 PSIFIs_IBD_Sudan AD06 - Accommodation And Food Service Activities 0 |
29. | 11 1 2014q1 2014q1 PSIFIs_IBD_TUR AD06 - Accommodation And Food Service Activities 0 |
30. | 11 1 2014q2 2014q2 PSIFIs_IBD_TUR AD06 - Accommodation And Food Service Activities 0 |
31. | 12 1 2014q1 2014q1 PSIFIs_IBD_UAE AD06 - Accommodation And Food Service Activities 323.46 |
32. | 12 1 2014q2 2014q2 PSIFIs_IBD_UAE AD06 - Accommodation And Food Service Activities 368.62 |
+------------------------------------------------------------------------------------------------------------------------------------+

.

      Thank you Andrew for your reply,

      Using your code has worked on the smaller data example that I posted however when applying it to the full-scale dataset,

      this error is outputted:

      . rename yr* value*

      . reshape long value, i(c_id s_id) j(qtr) string
      (j = 2014q1 2014q2 2014q3 2014q4 2015q1 2015q2 2015q3 2015q4 2016q1 20
      > 16q2 2016q3 2016q4 2017q1 2017q2 2017q3 2017q4 2018q1 2018q2 2018q3
      > 2018q4 2019q1 2019q2 2019q3 2019q4 2020q1 2020q2 2020q3 2020q4 2021q
      > 1 2021q2 2021q3 2021q4 2022q1 2022q2 2022q3 2022q4 2023q1 2023q2)
      variable id does not uniquely identify the observations
      Your data are currently wide. You are performing a reshape long.
      You specified i(c_id s_id) and j(qtr). In the current wide form,
      variable c_id s_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);

      . gen quarter= quarterly(qtr, "yq"), after(qtr)
      qtr not found
      r(111);

      . format quarter %tq
      variable quarter not found
      r(111);

      I have missing observations for some of the latter quarters in my dataset, is this what is causing the error and if so what would be the best method of dealing with the missing values? Currently, they are displayed as "."

      Comment

      • #4
        Missing identifiers should be the problem. How useful are such observations?

        Code:
        drop if missing(c_id) | missing(s_id)

        Comment

        Previous Next
        Working...
        X