Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Event Study: Reshaping Indices with multiple same variable name

    Hey everyone,

    I need your advice. I want to reshape my indices to long form. Basically I need the file to look like this in the end (this is an example data)
    Click image for larger version

Name:	Untitled3.png
Views:	1
Size:	11.7 KB
ID:	1430391


    This is how I prepare the data in excel
    Click image for larger version

Name:	Untitled.jpg
Views:	1
Size:	113.3 KB
ID:	1430389



    But when I import it to STATA it turns into this (I think it's because there are multiple same variable name)
    Click image for larger version

Name:	Untitled 2.png
Views:	1
Size:	17.9 KB
ID:	1430390



    So when I use this formula
    Code:
    reshape long index, i(sdate) j(reference1) string
    It didn't turn out as I wanted, since the renamed variable (v3, v4, v5, ...) stayed as variable on top and not being transformed...
    I have around 10,000 variables (so it goes until v8000 or something), so I'm afraid doing it manually one buy one is not a very good option.

    I tried renaming the variable with it's label using forearch, but it doesn't work.
    Code:
     
     foreach v of varlist var1 var2 var3 {    local x : variable label `v'    rename `v' `x' }

    Thank you.

    EDIT:
    Result of -describe-
    Code:
    v8209           float   %9.0g                 indexspcomp
    v8210           float   %9.0g                 indexspcomp
    v8211           float   %9.0g                 indexspcomp
    v8212           float   %9.0g                 indexhngkngi
    v8213           float   %9.0g                 indexftallsh
    v8214           float   %9.0g                 indexspcomp
    v8215           float   %9.0g                 indexibex35i
    v8216           float   %9.0g                 indexftallsh
    v8217           float   %9.0g                 indexftallsh
    v8218           float   %9.0g                 indexftallsh
    v8219           float   %9.0g                 indexspcomp
    v8220           float   %9.0g                 indexspcomp
    v8221           float   %9.0g                 indexspcomp
    v8222           float   %9.0g                 indexspcomp
    I don't think that it violates the variable name rules (no spaces, no non-alphanumeric characters, and none of them exceed 15 characters)

    Result from running -dataex-
    Code:
    . dataex
    input statement exceeds linesize limit. Try specifying fewer variables
    Last edited by Arissa Lara; 17 Feb 2018, 13:12.

  • #2
    Please read the Forum FAQ for excellent advice on how to post questions in a way that maximizes your chances of getting a helpful and timely response. Among the things you will learn from doing that are:

    1. Screen shots of Stata data sets are close to useless. Example data should always be posted by using the -dataex- command. You will also learn how to get -dataex-, if you don't already have it, and how to use it.

    2. Statements like "I tried renaming the variable with it's label using forearch, but it doesn't work" are completely useless. There are many ways you might have "tried renaming the variable with its label using foreach" and many ways in which those might not "work." So you need to show the exact code you tried, and show the exact results (including any error messages) Stata gave you, readably presented between code delimiters. (FAQ #12 will also explain code delimiters.) It just isn't possible to troubleshoot imaginary code.

    The more you help others understand your problem, in exact detail, the better they will be able to help you.

    In addition to the advice above, your inability to rename the variables using their labels may relate to some property of the variables themselves (e.g. they contain blanks or other characters not permissible in variable names, or perhaps they are too long, etc.) So you need to convey what these variable labels look like. Unfortunately, -dataex- does not include that information, because it is only in rare cases like this one that it is relevant. So, in this case, in addition to using -dataex-, also run -describe- and post results from that. With 8,000 variable, you should not post all of the results of -describe-, but show enough that we can get a good sense of how the variable labels look.


    Comment


    • #3
      Originally posted by Clyde Schechter View Post
      Please read the Forum FAQ for excellent advice on how to post questions in a way that maximizes your chances of getting a helpful and timely response. Among the things you will learn from doing that are:

      1. Screen shots of Stata data sets are close to useless. Example data should always be posted by using the -dataex- command. You will also learn how to get -dataex-, if you don't already have it, and how to use it.

      2. Statements like "I tried renaming the variable with it's label using forearch, but it doesn't work" are completely useless. There are many ways you might have "tried renaming the variable with its label using foreach" and many ways in which those might not "work." So you need to show the exact code you tried, and show the exact results (including any error messages) Stata gave you, readably presented between code delimiters. (FAQ #12 will also explain code delimiters.) It just isn't possible to troubleshoot imaginary code.

      The more you help others understand your problem, in exact detail, the better they will be able to help you.

      In addition to the advice above, your inability to rename the variables using their labels may relate to some property of the variables themselves (e.g. they contain blanks or other characters not permissible in variable names, or perhaps they are too long, etc.) So you need to convey what these variable labels look like. Unfortunately, -dataex- does not include that information, because it is only in rare cases like this one that it is relevant. So, in this case, in addition to using -dataex-, also run -describe- and post results from that. With 8,000 variable, you should not post all of the results of -describe-, but show enough that we can get a good sense of how the variable labels look.

      Thank you for your suggestions. I have adjusted my post based on your advice.


      Some additional information:
      I am running the analysis on Stata/SE 15.0 on Windows 10. I edited the data on Excel and import it to Stata in .csv

      Comment


      • #4
        Without understanding why you have so very many variables with exactly the same name, it's difficult to make sensible recommendations.

        You cannot use the variable labels in the way you wanted to rename your variables, because they are just the original duplicated variable names. You can only have one variable named indexspcomp.

        Here is an approach that may help, or may just yield nonsense for your purposes. Or it may fail because you have so very many identically named variables. What were you thinking when you prepared them that way in Excel? Certainly not that you were going to use them in Stata. And, in passing, why are you exporting the data from Excel into a CSV file? Why not just use import excel to read the data directly?

        Code:
        . describe
        
        Contains data
          obs:            10                          
         vars:             4                          
         size:           160                          
        ------------------------------------------------------------------------------------------------
                      storage   display    value
        variable name   type    format     label      variable label
        ------------------------------------------------------------------------------------------------
        indmoose        float   %9.0g                 indmoose
        v2              float   %9.0g                 indmoose
        v3              float   %9.0g                 indmoose
        indsquirrel     float   %9.0g                 indsquirrel
        ------------------------------------------------------------------------------------------------
        Sorted by: 
             Note: Dataset has changed since last saved.
        
        . foreach ind in indmoose indsquirrel {
          2.     ds, has(varlabel "`ind'")
          3.     rename(`r(varlist)') (`ind'(####)), addnumber
          4.         }
        indmoose  v2        v3
        indsquirrel
        
        . describe
        
        Contains data
          obs:            10                          
         vars:             4                          
         size:           160                          
        ------------------------------------------------------------------------------------------------
                      storage   display    value
        variable name   type    format     label      variable label
        ------------------------------------------------------------------------------------------------
        indmoose0001    float   %9.0g                 indmoose
        indmoose0002    float   %9.0g                 indmoose
        indmoose0003    float   %9.0g                 indmoose
        indsquirrel0001 float   %9.0g                 indsquirrel
        ------------------------------------------------------------------------------------------------
        Sorted by: 
             Note: Dataset has changed since last saved.

        Comment

        Working...
        X