Announcement

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

    Convert a dataset from wide to long

    Hello,

    I have a dataset (survey longitudinal data) with several variables that I want to convert from wide to long. Moreover I would like to change the name of the variables. I have several variables that report different month and year. The variables that start with "ce_" mean "September 2020" and the ones that start with "cg_" mean "January 2021". I would like to have rows with the values of January 2020 and January 2021 and columns with only the name of the value. Moreover, I have other variables without prefix and others that start with "i_" (that are common to both surveys conducted in september 2020 and january 2021). I would like to keep the information of these variables in the dataset.
    In the case of the variables that start with "ce_" and "cg_" I would like to have something like this:

    semp. parent5plus. couple. sex_cv

    September 2020
    January 2021
    September 2020
    January 2021

    I send an example of the dataset below.
    Can you help me with this?
    Thank you in advance

    Code:
    * Example generated by -dataex-. For more info, type help dataex
clear
input long(pidp pid) int i_strata byte(ce_semp cg_semp cg_parent5plus ce_parent5plus cg_couple ce_couple cg_sex_cv ce_sex_cv i_qfhigh)
   76165  10689869    6  1 -8 2 2 1 1 2 2 -8
  280165  12430439   15 -8  . . 1 . 1 . 2 -8
  469205  13857142   25 -8  . . 2 . 2 . 2 15
  732365  15752658   43 -8 -8 2 2 2 2 1 1 -8
 1587125  17870879   65  3 -8 2 2 2 2 2 2 -8
 4849085 176725733  148  1 -8 2 2 1 1 1 1 -8
68002725  10023526    1  4 -8 2 2 2 2 2 2 -8
68008847        -8 2006  1 -8 2 2 2 2 2 2 -8
68010887        -8 2006  1 -8 2 2 1 1 2 2 -8
68031967        -8 2030  4  4 2 2 2 2 2 2 -8
68035365  10403086    4  4 -8 2 2 2 2 1 1 -8
68035367        -8 2030  1 -8 2 2 1 1 1 1 -8
68041487        -8 2042  1 -8 1 1 1 1 2 2 -8
68045567        -8 2042  1 -8 2 2 2 2 2 2 -8
68051007        -8 2054  2 -8 2 2 1 1 1 1 -8
68051011        -8 2054  1 -8 2 2 1 1 2 2 -8
68058487        -8 2054  4 -8 2 2 1 1 1 1 -8
68058491        -8 2054  4 -8 2 2 1 1 2 2 -8
68060531        -8 2054  1 -8 1 1 1 1 2 2 -8
68060533 160066204    6  4 -8 2 2 1 1 2 2 -8
68060537 160066239    6  4 -8 2 2 1 1 1 1 -8
68061288        -8 2006 -8  . . 2 . 1 . 2 -8
68063247        -8 2066  1 -8 1 1 1 1 2 2 -8
68063927        -8 2066  1 -8 1 1 1 1 2 2 -8
68063931        -8 2066 -8 -8 1 1 1 1 1 1 -8
68064605  10653872    6  4 -8 2 2 1 1 1 1 -8
68064609  10653902    6  4 -8 2 2 1 1 2 2 -8
68068007        -8 2066  1 -8 2 2 1 1 1 1 -8
68068011        -8 2066  1  . . 2 . 2 . 2 -8
68068082        -8 2006  2 -8 2 2 1 1 1 1 -8
68097245  10913629    8  4 -8 2 2 2 2 2 2 -8
68097927        -8 2090  4 -8 2 2 2 2 2 2 -8
68112211        -8 2114  1  . . 1 . 1 . 2 -8
68120367        -8 2114  4 -8 2 2 2 2 2 2 -8
68120375        -8 2114  1 -8 1 2 2 2 2 2 -8
68125127        -8 2126  1 -8 1 1 1 1 2 2 -8
68125131        -8 2126  1 -8 2 2 1 1 1 1 -8
68125135        -8 2126  1 -8 2 2 2 2 2 2 -8
68133285  11218193   11  4 -8 2 2 2 2 2 2 -8
68133289  11218282   11  4 -8 2 2 1 1 2 2 -8
68136009  11234989   11  1 -8 1 1 2 2 2 2 -8
68137365  11240547   11 -8 -8 2 2 2 2 2 2 -8
68138045  11242787   11  4 -8 2 2 1 1 1 1 -8
68138049  11242817   11  4 -8 2 2 1 1 2 2 -8
68138051        -8 2138  4 -8 2 2 1 1 2 2 -8
68144847        -8 2138  1 -8 2 2 1 1 1 1 -8
68144851        -8 2138  1 -8 1 1 1 1 2 2 -8
68148247        -8 2150 -8 -8 2 2 1 1 1 1 -8
68148251        -8 2150  4  . . 2 . 1 . 2 -8
68150967        -8 2150  1  . . 2 . 1 . 1 -8
68150971        -8 2150  1 -8 2 2 1 1 2 2 -8
68150975        -8 2150  1 -8 2 2 2 2 1 1 -8
68155047        -8 2150 -8  4 1 1 1 1 2 2 -8
68155051        -8 2150  1  4 1 1 1 1 1 1 -8
68157771        -8 2150 -8  2 2 2 2 2 2 2 -8
68159131        -8 2150  1 -8 1 1 1 1 2 2 -8
68160485  11418567   11  1 -8 1 1 2 2 2 2 -8
68160489  11418591   11  1 -8 2 2 2 2 1 1 -8
68173407        -8 2174  4 -8 2 2 1 2 2 2 -8
68174767        -8 2174  4  . . 1 . 1 . 2 -8
68180887        -8 2174  1 -8 1 1 1 1 2 2 -8
68180891        -8 2174  1  . . 1 . 1 . 1 -8
68185647        -8 2186  4 -8 2 2 2 2 2 2 -8
68187687        -8 2186  4 -8 1 1 1 1 1 1 -8
68187691        -8 2186  4 -8 1 1 1 1 2 2 -8
68191771        -8 2186  1 -8 1 1 2 2 2 2 -8
68193127        -8 2186  4 -8 1 1 2 2 2 2 -8
68195167        -8 2186  4 -8 2 2 1 1 1 1 -8
68195171        -8 2186  4 -8 2 2 1 1 2 2 -8
68195851        -8 2186  1 -8 2 2 1 1 2 2 -8
68197211        -8 2198  1  . . 2 . 1 . 2 -8
68197887        -8 2198  1 -8 1 1 2 2 2 2 -8
68197899        -8 2198 -8 -8 2 1 2 2 2 2 -8
68197903        -8 2198 -8 -8 2 2 2 2 1 1 -8
68199247        -8 2198  1 -8 2 2 1 1 1 1 -8
68207407        -8 2198  4 -8 2 2 1 1 2 2 -8
68207411        -8 2198  4 -8 2 2 1 1 1 1 -8
68211487        -8 2210  4 -8 2 2 2 2 1 1 -8
68214207        -8 2210  1 -8 2 2 2 2 1 1 -8
68214887        -8 2210 -8  . . 1 . 1 . 1 -8
68214891        -8 2210  1  . . 1 . 1 . 2 -8
68216247        -8 2210  1 -8 1 1 1 1 2 2 -8
68218287        -8 2210 -8 -8 2 2 2 2 1 1 -8
68230527        -8 2222 -8  . . 1 . 2 . 2 -8
68231223        -8 2222  4 -8 2 2 2 2 2 2 -8
68238011        -8 2234  1 -8 2 2 1 1 2 2 -8
68262487        -8 2258  2 -8 2 2 1 1 1 1 -8
68266567        -8 2258  4 -8 2 2 2 2 2 2 -8
68278127        -8 2270  4 -8 2 2 2 2 2 2 -8
68288327        -8 2282  1 -8 1 1 1 1 2 2 -8
68288331        -8 2282  1 -8 1 1 1 1 1 1 -8
68291731        -8 2282  4 -8 2 2 2 2 2 2 -8
68293087        -8 2282  4 -8 1 1 1 1 2 2 -8
68293091        -8 2282  1 -8 1 1 1 1 1 1 -8
68293095        -8 2282  1 -8 1 1 1 1 1 1 -8
68293099        -8 2282  1 -8 2 2 1 1 1 1 -8
68293168        -8 2054  4 -8 2 2 2 2 1 1 -8
68294447        -8 2294  4  4 2 2 1 1 1 1 -8
68294451        -8 2294  1  1 2 2 1 1 2 2 -8
68297845  12521361   15  4 -8 2 2 1 1 1 1 -8
end
label values pid pid
label def pid -8 "inapplicable", modify
label values i_strata i_strata
label values ce_semp ce_semp
label def ce_semp -8 "inapplicable", modify
label def ce_semp 1 "Yes, employed only", modify
label def ce_semp 2 "Yes, self-employed only", modify
label def ce_semp 3 "Both employed and self-employed", modify
label def ce_semp 4 "No", modify
label values cg_semp cg_semp
label def cg_semp -8 "inapplicable", modify
label def cg_semp 1 "Yes, employed only", modify
label def cg_semp 2 "Yes, self-employed only", modify
label def cg_semp 4 "No", modify
label values cg_parent5plus cg_parent5plus
label def cg_parent5plus 1 "Yes", modify
label def cg_parent5plus 2 "No", modify
label values ce_parent5plus ce_parent5plus
label def ce_parent5plus 1 "Yes", modify
label def ce_parent5plus 2 "No", modify
label values cg_couple cg_couple
label def cg_couple 1 "Yes", modify
label def cg_couple 2 "No", modify
label values ce_couple ce_couple
label def ce_couple 1 "Yes", modify
label def ce_couple 2 "No", modify
label values cg_sex_cv cg_sex_cv
label def cg_sex_cv 1 "Male", modify
label def cg_sex_cv 2 "Female", modify
label values ce_sex_cv ce_sex_cv
label def ce_sex_cv 1 "Male", modify
label def ce_sex_cv 2 "Female", modify
label values i_qfhigh i_qfhigh
label def i_qfhigh -8 "inapplicable", modify
label def i_qfhigh 15 "Other school (inc. school leaving exam certificate or matriculation)", modify
    Tags: long, longitudinal data, panel data, wide
  • #2
    Code:
    reshape long @_semp @_parent5plus @_couple @_sex_cv, i(pidp) j(_j) string
replace _j = cond(_j=="ce","September 2020","January 2021")
    • 2 likes

    Comment

    • #3
      Hello,

      Since I have a big dataset with several variables that can be use in my analysis, is there a command that converts all the variables in my dataset from wide to long (without the need to write them in the stata command one by one)? At the same time this command should also change the “ce_” to “September 202” and “cg_” to “January 2021” (and put this information in rows just like I asked in the previous question).
      Thank you very much in advance.

      Comment

      • #4
        Code:
        ds cg_* ce_*
local varlist = ustrregexra("`r(varlist)'","^(cg|ce)_","@_")
reshape long `varlist', i(pidp) j(_j) string
replace _j = cond(_j=="ce","September 2020","January 2021")

        Comment

        • #5
          Hello,

          Thank you very much for your message. I tried to run this command but appears the following error: "variable 0_welsh implied name too long".
          I tried to delete this variable but it always appears a similar error with other variables. Can you help me to solve this problem?
          Thank you in advance.
          Last edited by Ana Vasconcelos; 23 Mar 2022, 17:28.

          Comment

          • #6
            Please check the length of variable name first with addition to charactors you want to suffix, should not be greater than 32 characters. Search for length of variable command

            Comment

            Previous Next
            Working...
            X