Dates as collumn headers

[Olena Onishchenko]

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.

* 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.

[wbuchanan]

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) :

. 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.

[Olena Onishchenko]

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:

* 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:

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.

[Carole J. Wilson]

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?

[wbuchanan]

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.

[Nick Cox]

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

. di mdy(3,16,2016)
20529

so if these are integer dates their origin predates 1 January 1960.

[Olena Onishchenko]

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:

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.

[Carole J. Wilson]

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):

gen stata_date=date-21916
format %td stata_date

list stata_date, clean noobs

    stata_d~e  
    05jan2004  
    06jan2004  
    07jan2004  
    08jan2004  
    09jan2004  
    12jan2004  
    13jan2004

[wbuchanan]

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.

[Olena Onishchenko]

Thank you Carole.

I confirm that the dates yielded match the original dates.