Creating a new date variable and converting it to %td format

[Susan Gutierrez]

Hi all,

I have a hopefully simple formatting question for dates. Most resources I've found on dates focus on how to convert existing dates in the dataset from string format, rather than generating a new date variable from scratch.

I am studying hospital readmissions. I would like to create a new date variable "end_study" that represents the end of the study period and equals "10/1/2015" for all entries. I will need to reformat it from a string variable to a floating variable so that I can calculate days between last admission and end of study. Is there a straightforward way to do this?

I have tried a couple of different approaches, no luck yet. Here was my most recent attempt:
* Create "end_of_study" variable that is equal to the admission date (string variable), and then change the date to my desired date of 10/01/2015.

 
gen end_of_study = admit date
replace end_of_study = 10/01/2015

When I try to replace end_of_study, I get the alert "type mismatch".

* Example generated by -dataex-. For more info, type help dataex
clear
input float patient_id str10 admit_date float(a_date start end) str10 end_of_study
1 "11/13/2006" 17118  452  491 "11/13/2006"
1 "01/15/2007" 17181  515  606 "01/15/2007"
1 "12/22/2006" 17157  491  515 "12/22/2006"
1 "08/14/2006" 17027  361  375 "08/14/2006"
1 "05/15/2007" 17301  635  680 "05/15/2007"
1 "08/28/2006" 17041  375  452 "08/28/2006"
1 "04/16/2007" 17272  606  635 "04/16/2007"
1 "06/29/2007" 17346  680    . "06/29/2007"
2 "04/28/2007" 17284  345  732 "04/28/2007"
2 "05/19/2008" 17671  732    . "05/19/2008"
2 "03/27/2007" 17252  313  345 "03/27/2007"
3 "04/13/2011" 18730    0  396 "04/13/2011"
3 "07/02/2012" 19176  446  580 "07/02/2012"
3 "11/02/2013" 19664  934 1005 "11/02/2013"
3 "02/09/2014" 19763 1033 1058 "02/09/2014"
3 "05/06/2015" 20214 1484 1521 "05/06/2015"
3 "01/07/2015" 20095 1365 1421 "01/07/2015"
3 "07/27/2015" 20296 1566    . "07/27/2015"
3 "08/21/2014" 19956 1226 1268 "08/21/2014"
3 "06/12/2015" 20251 1521 1566 "06/12/2015"
3 "04/10/2014" 19823 1093 1136 "04/10/2014"
3 "05/29/2014" 19872 1142 1164 "05/29/2014"
3 "05/23/2014" 19866 1136 1142 "05/23/2014"
3 "03/04/2015" 20151 1421 1484 "03/04/2015"
3 "11/27/2012" 19324  594  602 "11/27/2012"
3 "06/30/2013" 19539  809  830 "06/30/2013"
3 "12/01/2014" 20058 1328 1349 "12/01/2014"
3 "11/13/2012" 19310  580  594 "11/13/2012"
3 "12/05/2012" 19332  602  766 "12/05/2012"
3 "05/18/2013" 19496  766  809 "05/18/2013"
3 "07/21/2013" 19560  830  848 "07/21/2013"
3 "06/20/2014" 19894 1164 1226 "06/20/2014"
3 "05/13/2012" 19126  396  446 "05/13/2012"
3 "12/22/2014" 20079 1349 1365 "12/22/2014"
3 "08/08/2013" 19578  848  934 "08/08/2013"
3 "01/12/2014" 19735 1005 1033 "01/12/2014"
3 "10/02/2014" 19998 1268 1328 "10/02/2014"
3 "03/06/2014" 19788 1058 1093 "03/06/2014"
4 "05/05/2007" 17291  910  932 "05/05/2007"
4 "04/13/2007" 17269  888  910 "04/13/2007"
4 "10/15/2007" 17454 1073 1218 "10/15/2007"
4 "05/21/2008" 17673 1292    . "05/21/2008"
4 "05/27/2007" 17313  932  980 "05/27/2007"
4 "07/14/2007" 17361  980 1024 "07/14/2007"
4 "08/27/2007" 17405 1024 1073 "08/27/2007"
4 "03/08/2008" 17599 1218 1292 "03/08/2008"
5 "04/28/2012" 19111  562  626 "04/28/2012"
5 "03/12/2012" 19064  515  555 "03/12/2012"
5 "01/03/2012" 18995  446  506 "01/03/2012"
5 "11/01/2011" 18932  383  446 "11/01/2011"
5 "09/02/2011" 18872  323  383 "09/02/2011"
5 "07/01/2012" 19175  626    . "07/01/2012"
5 "04/21/2012" 19104  555  562 "04/21/2012"
5 "10/14/2010" 18549    0  323 "10/14/2010"
5 "03/03/2012" 19055  506  515 "03/03/2012"
6 "02/17/2015" 20136 4161    . "02/17/2015"
6 "04/24/2008" 17646 1671 1698 "04/24/2008"
6 "01/22/2007" 17188 1213 1280 "01/22/2007"
6 "01/25/2011" 18652 2677 2710 "01/25/2011"
6 "03/30/2007" 17255 1280 1302 "03/30/2007"
6 "03/05/2009" 17961 1986 2094 "03/05/2009"
6 "10/31/2007" 17470 1495 1515 "10/31/2007"
6 "11/19/2006" 17124 1149 1201 "11/19/2006"
6 "11/20/2007" 17490 1515 1617 "11/20/2007"
6 "09/04/2010" 18509 2534 2584 "09/04/2010"
6 "08/30/2006" 17043 1068 1084 "08/30/2006"
6 "10/24/2010" 18559 2584 2606 "10/24/2010"
6 "10/27/2009" 18197 2222 2228 "10/27/2009"
6 "09/15/2006" 17059 1084 1095 "09/15/2006"
6 "03/02/2010" 18323 2348 2508 "03/02/2010"
6 "11/14/2006" 17119 1144 1149 "11/14/2006"
6 "11/30/2008" 17866 1891 1929 "11/30/2008"
6 "04/17/2012" 19100 3125 3413 "04/17/2012"
6 "03/01/2008" 17592 1617 1671 "03/01/2008"
6 "11/02/2009" 18203 2228 2274 "11/02/2009"
6 "06/09/2006" 16961  986  997 "06/09/2006"
6 "11/15/2010" 18581 2606 2637 "11/15/2010"
6 "12/25/2013" 19717 3742 4161 "12/25/2013"
6 "06/21/2009" 18069 2094 2210 "06/21/2009"
6 "06/20/2006" 16972  997 1013 "06/20/2006"
6 "08/09/2010" 18483 2508 2534 "08/09/2010"
6 "09/26/2006" 17070 1095 1121 "09/26/2006"
6 "06/18/2008" 17701 1726 1891 "06/18/2008"
6 "05/21/2008" 17673 1698 1726 "05/21/2008"
6 "12/16/2010" 18612 2637 2677 "12/16/2010"
6 "01/30/2013" 19388 3413 3742 "01/30/2013"
6 "10/22/2006" 17096 1121 1144 "10/22/2006"
6 "10/20/2011" 18920 2945 3125 "10/20/2011"
6 "12/18/2009" 18249 2274 2293 "12/18/2009"
6 "01/10/2007" 17176 1201 1213 "01/10/2007"
6 "01/07/2009" 17904 1929 1986 "01/07/2009"
6 "02/27/2011" 18685 2710 2928 "02/27/2011"
6 "10/03/2011" 18903 2928 2945 "10/03/2011"
6 "07/06/2006" 16988 1013 1068 "07/06/2006"
6 "01/06/2010" 18268 2293 2348 "01/06/2010"
6 "10/15/2009" 18185 2210 2222 "10/15/2009"
6 "04/19/2006" 16910  935  986 "04/19/2006"
6 "04/21/2007" 17277 1302 1322 "04/21/2007"
6 "05/11/2007" 17297 1322 1459 "05/11/2007"
6 "09/25/2007" 17434 1459 1495 "09/25/2007"
end
format %td a_date

Thanks in advance for any advice or resources.

[Ken Chui]

This approach:

gen end_of_study = admit date
replace end_of_study = 10/01/2015

of generating something as a place holder and the try to replace that with information that is irrelevant in both content and format is just going complicate your work. Do not do that. Just generate what you need from the scratch. Only do this "gen --> replace" if the information copied over is truly useful.

The goal can be achieved with this code. Notice that I had to drop the end_of_study that came with the sample, you may not need to run the first drop command:

drop end_of_study
gen end_of_study = date("10/01/2015", "MDY")
format end_of_study %td

[Jared Greathouse]

Just another thing real quick, if your date variable is in numeric form (e.g., 22300), you can do

xtset id date,  d

and it'll automatically convert it to the format above

[Clyde Schechter]

One other slight simplification of the suggestion in #2:

gen end_of_study = td(01oct2015)
format end_of_study %td

It's a few keystrokes shorter that way; it produces the same results.

That said, why do you need this end_of_study variable which is, in fact, a constant, not a variable. There are only a few situations in Stata where it is useful to a variable that does not vary. If all you want to do is use it to calculate a duration of an admission, you don't need it. You can just do

gen duration = td(01oct2015) - last_admission_date

That code is shorter, faster, easier to understand, and makes more efficient use of memory. It has no downside I can think of.

Added: There are two different ways of reckoning the duration of a hospital admission. The method shown above will treat admitted today and discharged tomorrow as a 1 day admission. The other way is to treat it as a 2 day admission--to get that just stick a +1 on the end of that command.

[Susan Gutierrez]

Thank you Ken Chui and Jared Greathouse! I recognize this was a straightforward question, appreciate you taking the time to answer.

Clyde Schechter: your observation is spot on. I needed this new variable to fill in the missing data for "end" for the final hospitalization per patient, to define the interval from the final admission to the end of the study. I used your code to create a new variable "a_to_end" and from there I was able to replace all missing values for the variable "end" with "a_to_end".

gen a_to_end = td(01oct2015) - a_date
replace end = a_to_end if missing(end)

If you know a way I could have accomplished this with one command rather than two, I would be curious to learn! Either way, the issue is resolved - thanks so much.

* Example generated by -dataex-. For more info, type help dataex
clear
input float(patient_id a_date start end a_to_end)
1 17027  361  375 3335
1 17041  375  452 3321
1 17118  452  491 3244
1 17157  491  515 3205
1 17181  515  606 3181
1 17272  606  635 3090
1 17301  635  680 3061
1 17346  680 3016 3016
2 17252  313  345 3110
2 17284  345  732 3078
2 17671  732 2691 2691
3 18730    0  396 1632
3 19126  396  446 1236
3 19176  446  580 1186
3 19310  580  594 1052
3 19324  594  602 1038
3 19332  602  766 1030
3 19496  766  809  866
3 19539  809  830  823
3 19560  830  848  802
3 19578  848  934  784
3 19664  934 1005  698
3 19735 1005 1033  627
3 19763 1033 1058  599
3 19788 1058 1093  574
3 19823 1093 1136  539
3 19866 1136 1142  496
3 19872 1142 1164  490
3 19894 1164 1226  468
3 19956 1226 1268  406
3 19998 1268 1328  364
3 20058 1328 1349  304
3 20079 1349 1365  283
3 20095 1365 1421  267
3 20151 1421 1484  211
3 20214 1484 1521  148
3 20251 1521 1566  111
3 20296 1566   66   66
4 17269  888  910 3093
4 17291  910  932 3071
4 17313  932  980 3049
4 17361  980 1024 3001
4 17405 1024 1073 2957
4 17454 1073 1218 2908
4 17599 1218 1292 2763
4 17673 1292 2689 2689
5 18549    0  323 1813
5 18872  323  383 1490
5 18932  383  446 1430
5 18995  446  506 1367
5 19055  506  515 1307
5 19064  515  555 1298
5 19104  555  562 1258
5 19111  562  626 1251
5 19175  626 1187 1187
6 16910  935  986 3452
6 16961  986  997 3401
6 16972  997 1013 3390
6 16988 1013 1068 3374
6 17043 1068 1084 3319
6 17059 1084 1095 3303
6 17070 1095 1121 3292
6 17096 1121 1144 3266
6 17119 1144 1149 3243
6 17124 1149 1201 3238
6 17176 1201 1213 3186
6 17188 1213 1280 3174
6 17255 1280 1302 3107
6 17277 1302 1322 3085
6 17297 1322 1459 3065
6 17434 1459 1495 2928
6 17470 1495 1515 2892
6 17490 1515 1617 2872
6 17592 1617 1671 2770
6 17646 1671 1698 2716
6 17673 1698 1726 2689
6 17701 1726 1891 2661
6 17866 1891 1929 2496
6 17904 1929 1986 2458
6 17961 1986 2094 2401
6 18069 2094 2210 2293
6 18185 2210 2222 2177
6 18197 2222 2228 2165
6 18203 2228 2274 2159
6 18249 2274 2293 2113
6 18268 2293 2348 2094
6 18323 2348 2508 2039
6 18483 2508 2534 1879
6 18509 2534 2584 1853
6 18559 2584 2606 1803
6 18581 2606 2637 1781
6 18612 2637 2677 1750
6 18652 2677 2710 1710
6 18685 2710 2928 1677
6 18903 2928 2945 1459
6 18920 2945 3125 1442
6 19100 3125 3413 1262
6 19388 3413 3742  974
6 19717 3742 4161  645
6 20136 4161  226  226
end
format %td a_date

[Clyde Schechter]

replace end = td(01oct2015) - a_date if missing(end)

[Susan Gutierrez]

That worked perfect, thanks Clyde Schechter