merge dataset by variable Date ( problem: numeric and string variables)

[Rosa Marchesini]

Hi,

I have 2 datasets that need to be merged in one using variable Date (both MDY). The master dataset contains financial data on stock prices and the using dataset contains data about terrorists attacks.
However, I am not able to merge them due to differences in storage type. Master's Date variable is Int whilst of using is string.
When I try to translate it into a date variable STATA gives: 8,236 missing values generated (all values) or the error: type mismatch.
And when I try to destring the string variable, the values change and the values do not match with the other Date variable.

What can I do?

Thank you in advance,

Rosa

[Nick Cox]

Give examples please of both date variables. FAQ Advice #12 explains.

"When I try to translate it into a date variable": please show the code you used. FAQ Advice #12 again.

destringing a date variable is almost never the right thing to do. Just about the only exceptions are that years that are string such as "2018" can and should be pushed through destring. Otherwise dates such as "11/16/2018" will just be turned into numbers that don't correspond to Stata dates.

[Rosa Marchesini]

Examples of my date variables:
Date1
6/23/2007
6/22/2007
6/21/2007
6/18/2007
6/17/2007

Date2
6/16/2016
6/17/2007
6/17/2009
6/17/2009

However when I use the dataex command, the data looks different for 1 of the datasets:

dataex Date

----------------------- copy starting from the next line -----------------------
[CODE]
* Example generated by -dataex-. To install: ssc install dataex
clear
input str20 Date
"1/1/00"
"1/1/00"
"1/1/08"
"1/1/08"
"1/1/08"
"1/1/08"
"1/1/11"
"1/1/11"
"1/1/12"
"1/1/12"
"1/1/12"
"1/1/12"
"1/1/13"
"1/1/13"
"1/1/13"
"1/1/13"
"1/1/15"
"1/1/15"
"1/1/16"
"1/1/17"
"1/1/17"
"1/10/07"
"1/10/07"
"1/10/08"
"1/10/08"
"1/10/11"
"1/10/11"
"1/10/11"
"1/10/11"
"1/10/11"
"1/10/11"
"1/10/11"
"1/10/11"
"1/10/11"

and

dataex Date

----------------------- copy starting from the next line -----------------------
[CODE]
* Example generated by -dataex-. To install: ssc install dataex
clear
input int Date
21441
21440
21439
21438
21437
21434
21433
21432
21431
21427
21426
21425
21424
21423
21420
21419
21417
21416
21413
21412
21411
21410
21409
21406
21405
21403
21402
21399
21398
21397
21396
21395
21392
21391
21390
21389
21388
21385
21384
21383
21382
21381
21378
21377
21376
21375
21374
21371
21370
21369
21368
21367
21364
21363
21362
21361
21360
21357


Trying to translate it in a date variable I used : gen edate =date( Date , "MDY" [, 2017])
(8,236 missing values generated)

[Nick Cox]

Thanks for the detail.

This works for me for your example.

gen edate = daily( Date , "MDY" , 2017)
format edate %td