Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Generate highest monthly observation from daily data

    Dear Statalist,

    I have a dataset that consist of an unique identifier (ID) with a daily price.

    Code:
    * Example generated by -dataex-. To install: ssc install dataex
    clear
    input double permco float(id price year month day)
     7960 5         . 1986 1 13
     7960 5         . 1986 1 14
     7960 5      7.25 1986 1 15
     7960 5         7 1986 1 16
     7960 5     6.875 1986 1 17
     7960 5         . 1986 1 20
     7960 5         . 1986 1 21
    22156 7 12.833333 1963 1  2
    22156 7     12.75 1963 1  3
    22156 7 12.666667 1963 1  4
    22156 7 12.583333 1963 1  7
    22156 7 12.708333 1963 1  8
    22156 7     12.75 1963 1  9
    22156 7        13 1963 1 10
    22156 7        13 1963 1 11
    22156 7 12.854167 1963 1 14
    22156 7    12.875 1963 1 15
    22156 7    12.875 1963 1 16
    22156 7 12.854167 1963 1 17
    22156 7 12.916667 1963 1 18
    22156 7   12.9375 1963 1 21
    22156 7 12.916667 1963 1 22
    22156 7   13.1875 1963 1 23
    22156 7 13.354167 1963 1 24
    22156 7 13.583333 1963 1 25
    22156 7      13.5 1963 1 28
    22156 7 13.291667 1963 1 29
    22156 7 13.166667 1963 1 30
    22156 7 13.458333 1963 1 31
    22156 7 13.458333 1963 2  1
    22156 7   13.4375 1963 2  4
    22156 7 13.458333 1963 2  5
    22156 7 13.645833 1963 2  6
    22156 7      13.5 1963 2  7
    22156 7 13.583333 1963 2  8
    22156 7    13.625 1963 2 11
    22156 7    13.625 1963 2 12
    22156 7 13.833333 1963 2 13
    22156 7 13.958333 1963 2 14
    22156 7 13.916667 1963 2 15
    22156 7 13.895833 1963 2 18
    22156 7        14 1963 2 19
    22156 7 14.083333 1963 2 20
    22156 7      14.5 1963 2 21
    22156 7 14.666667 1963 2 25
    22156 7   14.5625 1963 2 26
    22156 7 14.416667 1963 2 27
    22156 7    13.875 1963 2 28
    22156 7 13.979167 1963 3  1
    22156 7 13.916667 1963 3  4
    22156 7    13.875 1963 3  5
    22156 7      13.5 1963 3  6
    22156 7 13.666667 1963 3  7
    22156 7 13.833333 1963 3  8
    22156 7        14 1963 3 11
    22156 7     13.75 1963 3 12
    22156 7 13.791667 1963 3 13
    22156 7     13.75 1963 3 14
    22156 7 13.541667 1963 3 15
    22156 7    13.625 1963 3 18
    22156 7 13.708333 1963 3 19
    22156 7 13.958333 1963 3 20
    22156 7 13.916667 1963 3 21
    end
    I would like to know how I can determine the highest price for each month for each unique identifier. As months do not always consist of an equal amount of days a simple 30-day rolling window does not satisfies. Any ideas? Much appreciated!!!

    Huib

  • #2
    I assume that permco is your unique identifier? You don't say that, but nothing else in your data looks suitable.

    First step is to get a real date variable: having separate variables for date, month, and year will get you into trouble in Stata sooner or later. You don't, strictly speaking, need it for the immediate purpose, but moving forward, you almost certainly will. Once that's done, it's straightforward.

    Code:
    gen real_date = mdy(month, day, year)
    format real_date %td
    
    gen real_month = mofd(real_date)
    format real_month %tm
    
    by permco real_month, sort: egen peak_price = max(price)
    Do familiarize yourself with -egen-. It is full of useful functions for data management. It is probably used several times a day by all regular Stata users. Read the manual section on -egen-.

    Comment

    Working...
    X