Announcement

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

    European Health for All database (HFA-DB) PIVOT format

    Fellow Stata users!

    I want to explore and analyse the WHO- European Health for All database (HFA-DB) Available: https://gateway.euro.who.int/en/datasets/

    However, the dataset is only dowloadable in pivot table format and I have never worked with pivot table format in Stata. Hence, I am at the moment a bit confused how to approach this. I would like to transform the whole dataset into wide (or long) format, two formats I am very familiar with.
    But for some, perhaps trivial, issues I am right now not able to wrap my head around these.
    I have loaded the csv. file into Stata, with first row as variable names, comma as separator, etc. Still it looks very wrong!

    So far i executed the commands:

    drop if COUNTRY_REGION==""
    contract SEX Measurecode COUNTRY_REGION v*, freq(sum_)
    reshape wide sum_, i(Measurecode) j(COUNTRY_REGION) string

    But, I always get the error code: "values of variable COUNTRY_REGION not unique within Measurecode"!

    The dataset looks like below with v4 going on until v152 (however some v* have no observations) and Measurecode going on til HFA_193. Sex exists as ALL, MALE and FEMALE.

    Measurecode SEX COUNTRY_REGION v4....
    HFA_1 ALL ALB
    HFA_1 ALL AND
    HFA_1 ALL ARM
    .
    .
    .

    As far as I understand HFA_* are the respective WHO-indicators/measures (variables). v1 til v74 are the values for the "years", with v74 being labeled "2020".

    I would appreciate any working solution!

    If you need more information, please do not hesitate to just ask in a comment.

    Best,

    Tom







    Tags: None
  • #2
    Provide a data example using the dataex command as recommended in FAQ Advice #12. A subsample of the dataset will do, e.g.,

    Code:
    dataex Measurecode - v10 in 1/20
    after importing the CSV file to Stata.

    Comment

    • #3
      ----------------------- copy starting from the next line -----------------------
      Code:
      * Example generated by -dataex-. To install: ssc install dataex
clear
input strL Measurecode str26 SEX str23 COUNTRY_REGION str24 v4 str51 v5 str19 v6 str62 v7 str21 v8 str75 v9 str16 v10
"HFA_1" "ALL" "ALB" "" "" "" "" "" "" ""
"HFA_1" "ALL" "AND" "" "" "" "" "" "" ""
"HFA_1" "ALL" "ARM" "" "" "" "" "" "" ""
"HFA_1" "ALL" "AUT" "" "" "" "" "" "" ""
"HFA_1" "ALL" "AZE" "" "" "" "" "" "" ""
"HFA_1" "ALL" "BLR" "" "" "" "" "" "" ""
"HFA_1" "ALL" "BEL" "" "" "" "" "" "" ""
"HFA_1" "ALL" "BIH" "" "" "" "" "" "" ""
"HFA_1" "ALL" "BGR" "" "" "" "" "" "" ""
"HFA_1" "ALL" "HRV" "" "" "" "" "" "" ""
"HFA_1" "ALL" "CYP" "" "" "" "" "" "" ""
"HFA_1" "ALL" "CZE" "" "" "" "" "" "" ""
"HFA_1" "ALL" "DNK" "" "" "" "" "" "" ""
"HFA_1" "ALL" "EST" "" "" "" "" "" "" ""
"HFA_1" "ALL" "FIN" "" "" "" "" "" "" ""
"HFA_1" "ALL" "FRA" "" "" "" "" "" "" ""
"HFA_1" "ALL" "GEO" "" "" "" "" "" "" ""
"HFA_1" "ALL" "DEU" "" "" "" "" "" "" ""
"HFA_1" "ALL" "GRC" "" "" "" "" "" "" ""
"HFA_1" "ALL" "HUN" "" "" "" "" "" "" ""
end
      ------------------ copy up to and including the previous line ------------------

      Listed 20 out of 11244 observations

      Comment

      • #4
        Addition: Measurecode is HFA_1 to HFA_193. These should be the actual variables/indicators. V4-V74 are the respective values from 1950 to 2020.

        Comment

        • #5
          Choose a specific measure code or loop if you need several. But something like...

          Code:
          * Example generated by -dataex-. For more info, type help dataex
clear
input strL Measurecode str26 SEX str23 COUNTRY_REGION str24 v4 str51 v5 str19 v6 str62 v7 str21 v8 str75 v9 str16 v10
"HFA_1" "ALL" "ALB" "" "" "" "" "" "" ""
"HFA_1" "ALL" "AND" "" "" "" "" "" "" ""
"HFA_1" "ALL" "ARM" "" "" "" "" "" "" ""
"HFA_1" "ALL" "AUT" "" "" "" "" "" "" ""
"HFA_1" "ALL" "AZE" "" "" "" "" "" "" ""
"HFA_1" "ALL" "BLR" "" "" "" "" "" "" ""
"HFA_1" "ALL" "BEL" "" "" "" "" "" "" ""
"HFA_1" "ALL" "BIH" "" "" "" "" "" "" ""
"HFA_1" "ALL" "BGR" "" "" "" "" "" "" ""
"HFA_1" "ALL" "HRV" "" "" "" "" "" "" ""
"HFA_1" "ALL" "CYP" "" "" "" "" "" "" ""
"HFA_1" "ALL" "CZE" "" "" "" "" "" "" ""
"HFA_1" "ALL" "DNK" "" "" "" "" "" "" ""
"HFA_1" "ALL" "EST" "" "" "" "" "" "" ""
"HFA_1" "ALL" "FIN" "" "" "" "" "" "" ""
"HFA_1" "ALL" "FRA" "" "" "" "" "" "" ""
"HFA_1" "ALL" "GEO" "" "" "" "" "" "" ""
"HFA_1" "ALL" "DEU" "" "" "" "" "" "" ""
"HFA_1" "ALL" "GRC" "" "" "" "" "" "" ""
"HFA_1" "ALL" "HUN" "" "" "" "" "" "" ""
end

keep if Measurecode=="HFA_1"
rename v* HFA1_#, addnumber(1950)
drop Measurecode SEX
reshape long HFA1_, i(COUNTRY_REGION) j(year)
          Res.:

          Code:
          . l, sepby(COUNTRY_REGION)

     +-------------------------+
     | COUNTR~N   year   HFA1_ |
     |-------------------------|
  1. |      ALB   1950         |
  2. |      ALB   1951         |
  3. |      ALB   1952         |
  4. |      ALB   1953         |
  5. |      ALB   1954         |
  6. |      ALB   1955         |
  7. |      ALB   1956         |
     |-------------------------|
  8. |      AND   1950         |
  9. |      AND   1951         |
 10. |      AND   1952         |
 11. |      AND   1953         |
 12. |      AND   1954         |
 13. |      AND   1955         |
 14. |      AND   1956         |
     |-------------------------|
 15. |      ARM   1950         |
 16. |      ARM   1951         |
 17. |      ARM   1952         |
 18. |      ARM   1953         |
 19. |      ARM   1954         |
 20. |      ARM   1955         |
 21. |      ARM   1956         |
     |-------------------------|
 22. |      AUT   1950         |
 23. |      AUT   1951         |
 24. |      AUT   1952         |
 25. |      AUT   1953         |
 26. |      AUT   1954         |
 27. |      AUT   1955         |
 28. |      AUT   1956         |
     |-------------------------|
 29. |      AZE   1950         |
 30. |      AZE   1951         |
 31. |      AZE   1952         |
 32. |      AZE   1953         |
 33. |      AZE   1954         |
 34. |      AZE   1955         |
 35. |      AZE   1956         |
     |-------------------------|
 36. |      BEL   1950         |
 37. |      BEL   1951         |
 38. |      BEL   1952         |
 39. |      BEL   1953         |
 40. |      BEL   1954         |
 41. |      BEL   1955         |
 42. |      BEL   1956         |
     |-------------------------|
 43. |      BGR   1950         |
 44. |      BGR   1951         |
 45. |      BGR   1952         |
 46. |      BGR   1953         |
 47. |      BGR   1954         |
 48. |      BGR   1955         |
 49. |      BGR   1956         |
     |-------------------------|
 50. |      BIH   1950         |
 51. |      BIH   1951         |
 52. |      BIH   1952         |
 53. |      BIH   1953         |
 54. |      BIH   1954         |
 55. |      BIH   1955         |
 56. |      BIH   1956         |
     |-------------------------|
 57. |      BLR   1950         |
 58. |      BLR   1951         |
 59. |      BLR   1952         |
 60. |      BLR   1953         |
 61. |      BLR   1954         |
 62. |      BLR   1955         |
 63. |      BLR   1956         |
     |-------------------------|
 64. |      CYP   1950         |
 65. |      CYP   1951         |
 66. |      CYP   1952         |
 67. |      CYP   1953         |
 68. |      CYP   1954         |
 69. |      CYP   1955         |
 70. |      CYP   1956         |
     |-------------------------|
 71. |      CZE   1950         |
 72. |      CZE   1951         |
 73. |      CZE   1952         |
 74. |      CZE   1953         |
 75. |      CZE   1954         |
 76. |      CZE   1955         |
 77. |      CZE   1956         |
     |-------------------------|
 78. |      DEU   1950         |
 79. |      DEU   1951         |
 80. |      DEU   1952         |
 81. |      DEU   1953         |
 82. |      DEU   1954         |
 83. |      DEU   1955         |
 84. |      DEU   1956         |
     |-------------------------|
 85. |      DNK   1950         |
 86. |      DNK   1951         |
 87. |      DNK   1952         |
 88. |      DNK   1953         |
 89. |      DNK   1954         |
 90. |      DNK   1955         |
 91. |      DNK   1956         |
     |-------------------------|
 92. |      EST   1950         |
 93. |      EST   1951         |
 94. |      EST   1952         |
 95. |      EST   1953         |
 96. |      EST   1954         |
 97. |      EST   1955         |
 98. |      EST   1956         |
     |-------------------------|
 99. |      FIN   1950         |
100. |      FIN   1951         |
101. |      FIN   1952         |
102. |      FIN   1953         |
103. |      FIN   1954         |
104. |      FIN   1955         |
105. |      FIN   1956         |
     |-------------------------|
106. |      FRA   1950         |
107. |      FRA   1951         |
108. |      FRA   1952         |
109. |      FRA   1953         |
110. |      FRA   1954         |
111. |      FRA   1955         |
112. |      FRA   1956         |
     |-------------------------|
113. |      GEO   1950         |
114. |      GEO   1951         |
115. |      GEO   1952         |
116. |      GEO   1953         |
117. |      GEO   1954         |
118. |      GEO   1955         |
119. |      GEO   1956         |
     |-------------------------|
120. |      GRC   1950         |
121. |      GRC   1951         |
122. |      GRC   1952         |
123. |      GRC   1953         |
124. |      GRC   1954         |
125. |      GRC   1955         |
126. |      GRC   1956         |
     |-------------------------|
127. |      HRV   1950         |
128. |      HRV   1951         |
129. |      HRV   1952         |
130. |      HRV   1953         |
131. |      HRV   1954         |
132. |      HRV   1955         |
133. |      HRV   1956         |
     |-------------------------|
134. |      HUN   1950         |
135. |      HUN   1951         |
136. |      HUN   1952         |
137. |      HUN   1953         |
138. |      HUN   1954         |
139. |      HUN   1955         |
140. |      HUN   1956         |
     +-------------------------+
          • 1 like

          Comment

          • #6
            Thank you! I did not quite get to the solution I want, so I tried a different approach since no loop worked:

            drop v75-v152
            drop if v74!=""
            describe v4
            describe Measurecode
            generate indicator = Measurecode if Measurecode <= string(24)
            describe indicator
            recast str24 indicator, force
            recast str24 v*, force
            describe v4-v74
            describe indicator
            gen indicator_year = "A"
            recast str24 indicator_year, force
            replace indicator_year=string(v4-v74, indicator)

            However, now I always get the error "type mismatch r(109);". I really do not understand why. So again I would greatly appreciate any help!

            Best,

            Tom

            Comment

            • #7
              Someone else in the forum may be able to offer you feedback on your code, but I am more interested in what your final objective is. For example, you may have concluded that #2 works on one measure code, but you want to extract several measure codes, e.g., 1-24. Or maybe not, in which case you need to explain what your actual problem is. See the XY problem as a basis for my reasoning.
              • 1 like

              Comment

              Previous Next
              Working...
              X