Destring date variable

[Rochelle Zhang]

Dear all,
I have two date variables: anndate, and fdate, stored as string9, I want to delete observations that are more than 90days apart (adate – fdate >90), here is what I did

Step 1: convert string to date :
destring anndate,gen(a_date)
error : generate(newvarlist) invalid
+++++++++++
then I tried
gen a1_date=date(anndate, "MDY")
format a1_date %td
(130213 missing values generated)
Could you point out my mistake?

Thanks,

Rochelle

[Joe Canner]

Rochelle,

The second approach is what you want. There's nothing particularly wrong with what you did, but it's hard to tell what the problem is without knowing more about your data. What is the format of anndate? How many observations have missing data?

Regards,
Joe

[Nick Cox]

Applying destring to a string date is wrong. Even if the string were all numeric characters (e.g. "20131225") destring would not read it as a date.

However, I can't see why you got that error.

The date() function is the right way to produce a daily date from a string, but your error message suggests that something may be wrong. Without seeing any of those 130213 observations, or any others, we can't comment.

Your output looks jumbled: a message about missing values wouldn't follow a format statement.

So, to get better advice, show us more about the data please, e.g. what those dates look like.

[Rochelle Zhang]

As an example, anndate 18SEP2000,

type str9, format %9s

I ran
gen a1_date=date(anndate, "MDY")

and got (130213 missing values generated)

Thank you,

[Jeph Herrin]

It looks like you have Day, Month, Year (DMY), not Month, Day, Year (MDY).

Try

gen a1_date = date(anndate,"DMY")

[Rochelle Zhang]

Thank you Jeph!!!

[Funmi Chinedu]

Hi,

I have dates in string written as 13-AUG-2009 (str9, format %9s). I tired converting to stata dates following this step:
gen sdate = date (date, "DMY")
format sdate %td
but I had only missing values. Can you please show me where I went wrong?
Thank you.

[Clyde Schechter]

Well, if that's the exact code you typed, you forgot to tell us about the error message "invalid '('" you got after the -gen sdate = - statement. That statement never executed, which is why you get no results. But I don't know how the -format- statement could have run either, because there would be no sdate variable. So I'm having trouble figuring out what you actually did and how Stata actually responded.

That said, the following will do what you appear to need:

// NOTE THAT BLANK SPACE BETWEEN THE FUNCTION NAME, date, AND THE
// OPENING PARENTHESIS, (, ARE NOT ALLOWED IN STATA SYNTAX.
gen sdate = date(date, "DMY")
format sdate %td

[Carole J. Wilson]

You need to show your exact syntax and exact output (and preferably an example of your data) using the appropriate code block (read the FAQ).

* Example generated by -dataex-. To install: ssc install dataex
clear
input str11 date
"13-AUG-2009"
"13-AUG-2009"
"13-AUG-2009"
"13-AUG-2009"
"13-AUG-2009"
end

If I run the code you type gen sdate = date (date, "DMY"), I get the following error:

. gen sdate = date (date, "DMY")
invalid '(' 

Instead, I run: sdate = date(date, "DMY") , note there is no space between date and the open parentheses. I receive no error, and all is well:

. format sdate %td

. list

     +-------------------------+
     |        date       sdate |
     |-------------------------|
  1. | 13-AUG-2009   13aug2009 |
  2. | 13-AUG-2009   13aug2009 |
  3. | 13-AUG-2009   13aug2009 |
  4. | 13-AUG-2009   13aug2009 |
  5. | 13-AUG-2009   13aug2009 |
     +-------------------------+

[Joseph Coveney]

I have dates in string written as 13-AUG-2009 (str9, format %9s). . . . Can you please show me where I went wrong?

Sure.

display in smcl as text strlen("13-AUG-2009")

[Ajay Kohli]

Hi there,


I have a dataset with dates in below format, I need to convert it to the date format so that I can calculate the age by subtracting it from another date dataset in the similar format.

Please help me to convert the below string dates in the stata readable date format. I tried below command and it generated all missing value.

generate dobnum = date(c3_dob,"DMY")


------Dataset-----

* Example generated by -dataex-. To install: ssc install dataex
clear
input str9 c3_dob
"21-Jul-77"
""         
"12-Jan-78"
"20-Sep-77"
"08-Sep-77"
"14-Dec-77"
"14-Jan-78"
"31-Aug-77"
"29-May-77"
""         
"21-Sep-76"
"28-Mar-76"
""         
"12-Mar-76"
"05-Feb-76"
"03-Dec-75"
"08-Mar-76"
"30-Jan-76"
""         
"25-Mar-75"
"03-Jun-75"
"14-Jun-75"
"15-Oct-74"
"19-Feb-74"
"31-Jul-73"
"20-May-74"
"16-Sep-73"
""         
"25-May-73"
"12-Jun-73"
"07-Jun-73"
"12-Feb-73"
""         
"14-Dec-71"
"10-Apr-72"
"13-Feb-72"
"02-Apr-72"
"02-Oct-71"
"18-Nov-71"
"04-Oct-71"
"11-Feb-71"
"13-Mar-71"
"11-Feb-71"
"28-Sep-70"
"23-Sep-70"
"07-May-71"
"28-Nov-70"
""         
""         
""         
"11-May-70"
"11-Sep-69"
""         
""         
"09-Mar-73"
"22-Feb-73"
""         
"27-Aug-71"
"07-Sep-71"
""         
""         
""         
"07-Oct-69"
"22-May-70"
""         
"28-Apr-78"
""         
""         
""         
"20-Feb-78"
"01-Oct-76"
""         
""         
"08-Nov-76"
""         
"12-Mar-77"
"02-May-76"
"17-Jan-76"
""         
"26-Mar-76"
"25-Apr-76"
"26-May-75"
"15-Jun-75"
"17-Dec-74"
"21-Jan-75"
""         
"19-Feb-74"
"16-Jun-74"
"31-Aug-73"
"14-Nov-73"
"05-Apr-73"
"04-Jul-72"
"06-Mar-73"
"27-Dec-72"
"08-May-72"
"08-Feb-72"
"16-Feb-71"
""         
"28-May-71"
"20-Jan-71"
end

Please help.

Thanks!

[Joseph Coveney]

quietly generate int dobnum = date(c3_dob,"DM19Y")
format dobnum %dCY-N-D
list, noobs separator(0)

[Nick Cox]

Joseph Coveney gives excellent advice, but still watch out. If some of your years are 20??, you need something more complicated.

generate int dobnum = cond(substr(c3_dob, -2, 2) <= "19", daily(c3_dob, "DM20Y"), daily(c3_dob,"DM19Y")) 

daily() and date() are different names for the same code. I recommend use of daily() as more explicit.

In general, there is no short-cut beyond consultation of

help datetime

to understand here.

[Ajay Kohli]

Hi Nick,

Thanks Nick Cox for helping me out. Using this I was able to generate the dates. However, when now I am trying to get the age by subtracting the two dates, I am again getting missing value generated. I am using the below command to generate the age.

generate age = date("datenum","YMD") - date("donbnum","YMD")

Below is the dataset

* Example generated by -dataex-. To install: ssc install dataex
clear
input int(dobnum datenum)
6411 21484
   .     .
6586 21609
6472 21608
6460 21611
6557 20057
6588 21610
6452 21610
6358 21611
   .     .
6108 21609
5931 21611
   .     .
5915 21343
5879 21062
5815 21610
5911 21597
5873 21611
   .     .
5562 21049
5632 21336
5643 21340
5401 21608
5163 21608
4960 21610
5253 21610
5007 21610
   .     .
4893 21608
4911 21609
4906 21611
4791 21623
   .     .
4365 21610
4483 21338
4426 21608
4475 21608
4292 21611
4339 21485
4294 21483
4059 21609
4089 21611
4059 21609
3923 20057
3918 21611
4144 21624
3984 21490
   .     .
   .     .
   .     .
3783 21611
3541 21611
   .     .
   .     .
4816 21512
4801 21514
   .     .
4256 20036
4267 21498
   .     .
   .     .
   .     .
3567 21513
3794 21485
   .     .
6692 21499
   .     .
   .     .
   .     .
6625 21499
6118 21056
   .     .
   .     .
6156 21486
   .     .
6280 21498
5966 21498
5860 21497
   .     .
5929 21498
5959 21259
5624 21497
5644 21337
5464 21499
5499 21491
   .     .
5163 21489
5280 21499
4991 21499
5066 21499
4843 21609
4568 21483
4813 21486
4744 21519
4511 21489
4421 21489
4064 21484
   .     .
4165 21492
4037 21392
end
format %dCY-N-D dobnum
format %dCY-N-D datenum

Please help me with this. I need to get the age in years.



KR
Ambrish

[Nick Cox]

datenum and dobnum are already numeric date variables, so nothing to convert.

gen age = (datenum - dobnum) / 365.25

is often considered good enough in say biostatistics and epidemiology. Note that feeding a literal string to date() such as "datenum" is legal but useless, as no date information is included. Stata will not treat "datenum" as (the numeric variable datenum but converted to string).