Last observation of daily data for each month

[Marius Helseth]

Date IV
3/1/2000 27.81
3/2/2000 24.46
3/3/2000 22.65
3/6/2000 22.38
3/7/2000 26.36
3/8/2000 27.42
3/9/2000 26.45
3/10/2000 25.25
3/13/2000 27.99
3/14/2000 27.46
3/15/2000 28.17
3/16/2000 27.11
3/17/2000 26.1
3/20/2000 25.86
3/21/2000 26.11
3/22/2000 26.35
3/23/2000 27.66
3/24/2000 25.76
3/27/2000 24.94
3/28/2000 23.56
3/29/2000 23.86
3/30/2000 26.56
3/31/2000 27.01
4/3/2000 27.83
4/4/2000 26.95
4/5/2000 30.35
4/6/2000 27.74
4/7/2000 26.57
4/10/2000 26.51
4/11/2000 26.87
4/12/2000 26.25
4/13/2000 26.45
4/14/2000 26.36
4/17/2000 27.35
4/18/2000 26.7
4/19/2000 25.3
4/20/2000 21.56
4/25/2000 24.55
4/26/2000 24.65
4/27/2000 28.09
4/28/2000 26.29
5/2/2000 25.47
5/3/2000 27.68
5/4/2000 26.82
5/5/2000 25.49
5/8/2000 26.64
5/9/2000 28

Hi! I have some issues when Im trying to keep last day observation of each month, using these comands:

gen long first_of_next_month = dofm(mofd(Date)+1)
keep if Date + 1 == first_of_next_month

Some months are beeing removed, as you can see under:

Date IV first_of_next_month
1/31/2000 28.46 14641
2/29/2000 28.61 14670
3/31/2000 27.01 14701
5/31/2000 27.73 14762
6/30/2000 23.15 14792
7/31/2000 23.33 14823
10/31/2000 22.97 14915
11/30/2000 26.36 14945
1/31/2001 20.32 15007
2/28/2001 21.86 15035
4/30/2001 18.88 15096
5/31/2001 19.79 15127
7/31/2001 21.72 15188
8/31/2001 27.35 15219
10/31/2001 36.31 15280
11/30/2001 26.15 15310
1/31/2002 24.08 15372
2/28/2002 22.99 15400
4/30/2002 23.05 15461
5/31/2002 23.59 15492

As you can see, 30.04.2000 are beeing removed, I think the reason is because 29 and 30 of april 2000 landed on a weekend (the data is stock prices). How could I fix this problem? I want stata to understand that I want 28.04.2000 and so on, so I can keep the last trading day of each month.

Hope some og you could help me! Thank you in advance

[Nick Cox]

gen MDate = mofd(Date) 
bysort MDate (Date) : keep if _n == _N

[Marius Helseth]

Hi, Nick

I have tried this one before, but it doesn't help, it only keeps the last observation in the whole dataset, so the only one I have now is this:

Date IV MDate
3/1/2019 12.657 710

keep if _n == _N
(4,888 observations deleted)
So 4888 of my 4889 is beeing removed...

[Nick Cox]

Not so. You don't give a data example using dataex (contrary to FAQ Advice #12) but some surgery can produce one from you give. Then my code does what you ask:

* Example generated by -dataex-. To install: ssc install dataex
clear
input float Date double IV
14670 27.81
14671 24.46
14672 22.65
14675 22.38
14676 26.36
14677 27.42
14678 26.45
14679 25.25
14682 27.99
14683 27.46
14684 28.17
14685 27.11
14686  26.1
14689 25.86
14690 26.11
14691 26.35
14692 27.66
14693 25.76
14696 24.94
14697 23.56
14698 23.86
14699 26.56
14700 27.01
14703 27.83
14704 26.95
14705 30.35
14706 27.74
14707 26.57
14710 26.51
14711 26.87
14712 26.25
14713 26.45
14714 26.36
14717 27.35
14718  26.7
14719  25.3
14720 21.56
14725 24.55
14726 24.65
14727 28.09
14728 26.29
14732 25.47
14733 27.68
14734 26.82
14735 25.49
14738 26.64
14739    28
end
format %tdn/d/CY Date

gen MDate = mofd(Date)

bysort MDate (Date) : keep if _n == _N

list

     +---------------------------+
     |    IV        Date   MDate |
     |---------------------------|
  1. | 27.01   3/31/2000     482 |
  2. | 26.29   4/28/2000     483 |
  3. |    28    5/9/2000     484 |
     +---------------------------+

Naturally if you leave off the prefix command then

keep if _n == _N

keeps only the last observation. Perhaps go to

help _variables

if you don't understand.

[Marius Helseth]

Hi!

It worked! Thank you very much for your help!

Have a nice day!