Announcement

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

  • Dates as collumn headers

    Dear Stata users

    I have a dataset where column headers are dates. For each date there stocks which are in a portfolio for a particular date.

    Code:
    * Example generated by -dataex-. To install: ssc install dataex
    clear
    input str12(A B C D)
    "KR7001511005" "KR7025830001" "KR7001380005" "KR7033250002"
    "KR7074610007" "KR7018500009" "KR7033250002" "KR7007160005"
    "KR7011780004" "KR7001250000" "KR7001250000" "KR7003961000"
    "KR7011781002" "KR7001380005" "KR7009380007" "KR7001250000"
    end
    When I try to import data into Stata dates convert into letters (A,B, C...etc).

    I need to do two things:
    1. Keep dates as column headers.
    2. Reshape data into panel format. I used to use the following code: reshape long var (in my case should be date), i (I do not really have this variable).

    Any thought how to implement it?

    Thanks a lot in advance.

  • #2
    Olena Onishchenko it'd be helpful to know what file format your are reading from (e.g., since Stata won't allow dates for variable names it must be data stored in a different format). Are the dates formatted in a way that would create valid Stata variable names (e.g., must start with underscore/letter) :

    Code:
    . set obs 10
    number of observations (_N) was 0, now 10
    
    . g _x = .
    (10 missing values generated)
    
    . g x = .
    (10 missing values generated)
    
    . g 10mar2016 = .
    10mar2016 invalid name
    r(198);
    If not, you would need to rename the variables prior to import or rename them once imported.

    Comment


    • #3
      I have renamed variables attaching var_ before the date.

      I have reshaped my data into panel and now need to convert dates into date format.
      Here is my data:

      Code:
      * Example generated by -dataex-. To install: ssc install dataex
      clear
      input byte stock_count float date str5 Date str12 var_
      1 37991 "37991" "KR7001511005"
      1 37992 "37992" "KR7025830001"
      1 37993 "37993" "KR7001380005"
      1 37994 "37994" "KR7033250002"
      1 37995 "37995" "KR7001670009"
      1 37998 "37998" "KR7002700003"
      1 37999 "37999" "KR7007160005"
      end
      When I am trying to convert it to date format I use the following code:

      Code:
      generate Date2=date(Date,"DMY")
      format %tdNN/DD/CCYY Date2
      But it does not work giving dots in new Date2 variable.

      Where I went wrong?

      Thank you.
      Last edited by Olena Onishchenko; 15 Mar 2016, 18:23.

      Comment


      • #4
        Stata does not recognize this as a date. In the generate command, you are telling Stata that "37991" contains the day, month, and year, but Stata does not know how to parse this (nor do I). Can explain how this date is created?
        Stata/MP 14.1 (64-bit x86-64)
        Revision 19 May 2016
        Win 8.1

        Comment


        • #5
          Those look like the integer values for the date. Try setting the display format on the variable directly. The date function is used to convert human readable date strings to SIF dates.

          Comment


          • #6
            Do your dates have some origin like 1 January 1900? See http://www.stata.com/manuals14/ddatetime.pdf p.9ff.

            wbuchanan Billy: Note that recent dates are about 20000

            Code:
            . di mdy(3,16,2016)
            20529
            so if these are integer dates their origin predates 1 January 1960.
            Last edited by Nick Cox; 15 Mar 2016, 19:07.

            Comment


            • #7
              Initially my data comes from a spreadsheet and columns headers are dates. As Stata does not read dates as columns headers I attached "var_" to each variable and excel converted dates into integer. After that I reshaped data to panel format using the following code:

              Code:
              reshape long var_, i (stock_count)
              I obtained a column with dates (which is my ultimate goal) but Stata does not recognize it now.

              I have attached spreadhseet in order to show the original file.

              Thank you.
              Attached Files
              Last edited by Olena Onishchenko; 15 Mar 2016, 19:13.

              Comment


              • #8
                A quick google search seems to indicate that Excel dates - Stata's initial date (01jan1960=21916) will yield the correct date (you should confirm that the dates yielded match the original dates):

                Code:
                gen stata_date=date-21916
                format %td stata_date
                
                list stata_date, clean noobs
                
                    stata_d~e  
                    05jan2004  
                    06jan2004  
                    07jan2004  
                    08jan2004  
                    09jan2004  
                    12jan2004  
                    13jan2004


                Last edited by Carole J. Wilson; 15 Mar 2016, 19:36.
                Stata/MP 14.1 (64-bit x86-64)
                Revision 19 May 2016
                Win 8.1

                Comment


                • #9
                  Nick Cox definitely a good point. If Olena Onishchenko is storing the data in MS Excel it might solve some of the issue if the column is indicated as a date or datetime column in Excel. The additional metadata could make importing the values into Stata more consistent and would likely help to alleviate any problems that could arise due to MS Excel using two distinct epoch dates.

                  Comment


                  • #10
                    Thank you Carole.

                    I confirm that the dates yielded match the original dates.

                    Comment

                    Working...
                    X