Generate variable based on hourly data

[Joan Marti]

Hi, I have a variable for the day of the year and a variable for the hour of the day, such as:

DAY	HOUR	Dummy
21	13:51:08	0
21	14:32:01	0
21	17:45:48	1
22	10:12:16	1
22	13:15:01	1

The variable type of HOUR is double, while DAY is byte. I want to create a variable "Dummy" that takes the value of 1 if the observation happened after DAY 21 at 15:00 (I created the variable in the table above). I tried with the following code

gen dummy==0
replace dummy==1 if DAY > 21 & HOUR > 15:00:00?

but it says "15:00:00 invalid name". Does anyone know how to solve this?

Thank you very much!

[Andrew Musau]

You are dealing with time variables, so you need to show a data example using dataex. It is crucial here as the variable HOUR may be a time variable set to display hours, minutes and seconds. All this should be obvious to you after 84 posts, but if not, review FAQ Advice #12.

[Joan Marti]

Thank you Andrew, I agree with you completely I should have added a data example. Here it is:

* Example generated by -dataex-. For more info, type help dataex
clear
input double HOUR byte DAY
-11903710994000 17
-11903715262000 21
-11903717850000 21
-11903695868000 17
-11903715828000 26
-11903692125000 25
-11903695161000 18
-11903699051000 21
end
format %tchH:MM:SS HOUR

I do not understand why the format I see when I browse the data is different to the format I see here... I guess indeed the numbers here refer to hours, minutes and seconds?

Thank you again!

[William Lisowski]

Stata's "date and time" variables are complicated and there is a lot to learn. If you have not already read the very detailed Chapter 24 (Working with dates and times) of the Stata User's Guide PDF, do so now. If you have, it's time for a refresher. After that, the help datetime documentation will usually be enough to point the way. You can't remember everything; even the most experienced users end up referring to the help datetime documentation or back to the manual for details. But at least you will get a good understanding of the basics and the underlying principles. An investment of time that will be amply repaid.

All Stata manuals are included as PDFs in the Stata installation and are accessible from within Stata - for example, through the PDF Documentation section of Stata's Help menu.

[William Lisowski]

Can you tell us how your HOUR variable was created? It is a Stata datetime value, but when we look at the full date and time we see

 clonevar hour = HOUR

. format hour %tc

. list, clean

           HOUR   DAY                 hour  
  1.   13:36:46    17   14oct1582 13:36:46  
  2.   12:25:38    21   14oct1582 12:25:38  
  3.   11:42:30    21   14oct1582 11:42:30  
  4.   17:48:52    17   14oct1582 17:48:52  
  5.   12:16:12    26   14oct1582 12:16:12  
  6.   18:51:15    25   14oct1582 18:51:15  
  7.   18:00:39    18   14oct1582 18:00:39  
  8.   16:55:49    21   14oct1582 16:55:49

which is not a particularly helpful representation of time - Stata usually creates times with no date specified as 1Jan1960. It would be better to re-create HOUR correctly than to try to work around the problem you now have.

[Andrew Musau]

Thank you Andrew, I agree with you completely I should have added a data example. Here it is:

* Example generated by -dataex-. For more info, type help dataex
clear
input double HOUR byte DAY
-11903710994000 17
-11903715262000 21
-11903717850000 21
-11903695868000 17
-11903715828000 26
-11903692125000 25
-11903695161000 18
-11903699051000 21
end
format %tchH:MM:SS HOUR

I do not understand why the format I see when I browse the data is different to the format I see here... I guess indeed the numbers here refer to hours, minutes and seconds?

Thank you again!

As William remarks, you should check whether these times are correct. I will assume that the times represented by the variable HOUR are correct even though the dates are not. One way to verify this is

assert dofc(HOUR)==td(14oct1582)

Note the existence of convenience functions such as -tc()- and -td()-

di %tc -11903699051000

Res.:

. di %tc -11903699051000
14oct1582 16:55:49

Therefore, we can generate the wanted variable as below:

* Example generated by -dataex-. For more info, type help dataex
clear
input double HOUR byte DAY
-11903710994000 17
-11903715262000 21
-11903717850000 21
-11903695868000 17
-11903715828000 26
-11903692125000 25
-11903695161000 18
-11903699051000 21
end
format %tchH:MM:SS HOUR


g wanted= DAY>21 & HOUR> tc(14oct1582 15:00:00)

Res.:

. l, sep(0)

     +-------------------------+
     |     HOUR   DAY   wanted |
     |-------------------------|
  1. | 13:36:46    17        0 |
  2. | 12:25:38    21        0 |
  3. | 11:42:30    21        0 |
  4. | 17:48:52    17        0 |
  5. | 12:16:12    26        0 |
  6. | 18:51:15    25        1 |
  7. | 18:00:39    18        0 |
  8. | 16:55:49    21        0 |
     +-------------------------+

[Joan Marti]

Thank you very much for your responses! Very helpful!!