Recoding Date into numeric variable

[Sascha AbdelHadi]

Dear all,

in one of my recent projects I collected hierarchical data with 14 observations (1 observation a day) for every subject. Unfortunately, the only variable to identify the measuring time is the date variable.

Example:


ID Date
1 mm.dd.yy
1 mm.dd.yy
1 mm.dd.yy
1 mm.dd.yy
1 mm.dd.yy
2 mm.dd.yy
2 mm.dd.yy
2 mm.dd.yy
2 mm.dd.yy
2 mm.dd.yy

To save some time I asked myself now, if it's possible to recode the date variable into a nummeric variable in stata? Since every research subject had its own starting point, I need to take into account, that it's not that just one day can be transferred into a number. I would prefer a data structure like this:

ID Day
1 1
1 2
1 3
1 4
1 5
2 1
2 2
2 3
2 4
2 5


I'm really looking forward to your answers.Thanks in advance.


Best,
Sascha

[Nick Cox]

Real examples beat schematic examples almost any day. Please study https://www.statalist.org/forums/help#stata and act on it.

[Marcos Almeida]

Hazarding a (quite wild) guess, you may just want to use - sort - command:

 
. set obs 6
number of observations (_N) was 5, now 6

. gen var1 = 1

. replace var1 = 2 in 4/6
(3 real changes made)

. generate str var2 = "01/28/2017" in 1
(5 missing values generated)

. replace var2 = "02/15/2016" in 2
(1 real change made)

. replace var2 = "10/23/2015" in 3
(1 real change made)

. replace var2 = "05/17/2012" in 4
(1 real change made)

. replace var2 = "04/18/2010" in 5
(1 real change made)

. replace var2 = "11/19/2011" in 6
(1 real change made)

. gen var3 = date(var2, "MDY")

. format var3 %td

. list

     +-------------------------------+
     | var1         var2        var3 |
     |-------------------------------|
  1. |    1   01/28/2017   28jan2017 |
  2. |    1   02/15/2016   15feb2016 |
  3. |    1   10/23/2015   23oct2015 |
  4. |    2   05/17/2012   17may2012 |
  5. |    2   04/18/2010   18apr2010 |
     |-------------------------------|
  6. |    2   11/19/2011   19nov2011 |
     +-------------------------------+

.*/ up to this point, this is the information you were supposed to provide beforehand.

.*/  Whenever we follow the advices shared in the FAQ, extra time as well as guessing effort from forum members are spared.

. */ Now, going directly to the point, this is (hopefully) what you really wish to get:

. sort var1 var3

. list, sep(3)

     +-------------------------------+
     | var1         var2        var3 |
     |-------------------------------|
  1. |    1   10/23/2015   23oct2015 |
  2. |    1   02/15/2016   15feb2016 |
  3. |    1   01/28/2017   28jan2017 |
     |-------------------------------|
  4. |    2   04/18/2010   18apr2010 |
  5. |    2   11/19/2011   19nov2011 |
  6. |    2   05/17/2012   17may2012 |
     +-------------------------------+

Shall you really wish an integer representing the order, you can type:

. by var1, sort : egen float myrank = rank(var3)

To end, please read the Stata manual, particularly the excerpt about the way Stata deals with date and time variables.

For this, you may wish to type:

. help datetime

There, you will find helpful information.

To end, I just wish to underline that the date variables are estimated "numerically", exactly like the "data structure" you said to prefer.

The format you see above, not "unfortunately" as you thought, can be presented by Stata in a friendlier way: fortunately, instead of a somewhat arcane 20834 (you will find the reason for this, after taking a look at the manual), human brains can read as "23oct2015".

Hopefully that helps.