Dropping panel data observations for certain months only

[Benedikt Dieker]

Hello, I am encountering the following (probably relatively straightforward) problem and would appreciate any help.

I have panel data of monthly stock price data, with the date variable stored in long format, displayed in %td format.
Basically, what I want to do is to have yearly data, so dropping all monthly observations for the months of January to November inclusive.
Following this, as I want to merge this data with another dataset of yearly data, I want to change the date variable to a year variable that displays only the year.

CUSIP date price shares_outstanding equity
00020910 31jan1992 37 9188 339956
00020910 28feb1992 36.5 9188 335362
00020910 31mar1992 35.5 9281 329475.5
00020910 30apr1992 34.75 9281 322514.8
00020910 29may1992 36.5 9281 338756.5
00020910 30jun1992 31.875 9284 295927.5
00020910 31jul1992 35.5 9284 329582
00020910 31aug1992 36 9284 334224
00020910 30sep1992 34.25 9362 320648.5
00020910 30oct1992 32.875 9362 307775.8
00020910 30nov1992 32.75 9362 306605.5
00020910 31dec1992 31.875 9362 298413.8
00020910 29jan1993 30.75 9362 287881.5
00020910 26feb1993 19.875 14043 279104.6
00020910 31mar1993 18.875 13780 260097.5
00020910 30apr1993 18.25 13780 251485

I know that I can drop certain observations in string format if the string contains a certain substring. But if I string the date, I end up with the date in number of days from 1 Jan 1960...


Would appreciate any help, thanks.

[Nick Cox]

Your data example is helpful but needs surgery to be really helpful. As explained in FAQ Advice #12 using dataex is requested -- especially when date variables are concerned. Otherwise

help datetime

explains that the function month() pulls month of year out of daily dates and that the function year() pulls calendar year out of such. Those lead to a solution.

* Example generated by -dataex-. For more info, type help dataex
clear
input int CUSIP float date double price int shares_outstanding double equity
20910 11718     37  9188   339956
20910 11746   36.5  9188   335362
20910 11778   35.5  9281 329475.5
20910 11808  34.75  9281 322514.8
20910 11837   36.5  9281 338756.5
20910 11869 31.875  9284 295927.5
20910 11900   35.5  9284   329582
20910 11931     36  9284   334224
20910 11961  34.25  9362 320648.5
20910 11991 32.875  9362 307775.8
20910 12022  32.75  9362 306605.5
20910 12053 31.875  9362 298413.8
20910 12082  30.75  9362 287881.5
20910 12110 19.875 14043 279104.6
20910 12143 18.875 13780 260097.5
20910 12173  18.25 13780   251485
end
format %td date

gen month = month(date)
gen year = year(date)

keep if month == 12

list

     +-----------------------------------------------------------------+
     | CUSIP        date    price   shares~g     equity   month   year |
     |-----------------------------------------------------------------|
  1. | 20910   31dec1992   31.875       9362   298413.8      12   1992 |
     +-----------------------------------------------------------------+

Note that you don't need to generate a month variable, as

keep if month(date) == 12

would do what you wanted.

[Benedikt Dieker]

Nick, thank you for your prompt solution. Also, noted the comment regarding data examples going forward.