Five-week median value

[Stephen Okiya]

Hi Stata users,
I am using Stata 17 and would like to find a five-week median value for each date in my time series data. Example of my data is below

* Example generated by -dataex-. For more info, type help dataex
clear
input str10 date byte(workplace residence)
"2020-03-01"   3  0
"2020-03-02"   3  1
"2020-03-03"   2  0
"2020-03-04"   2  0
"2020-03-05"   3  0
"2020-03-06"   5  1
"2020-03-07"   5 -1
"2020-03-08"   4  0
"2020-03-09"   3  0
"2020-03-10"   2  0
"2020-03-11"   1  1
"2020-03-12"   1  1
"2020-03-13"   3  1
"2020-03-14"   2  0
"2020-03-15"   1  1
"2020-03-16"   0  1
"2020-03-17"   0  1
"2020-03-18"   0  1
"2020-03-19"   0  1
"2020-03-20"   2  2
"2020-03-21"   1  2
"2020-03-22"  -3  6
"2020-03-23" -10  7
"2020-03-24" -20 11
"2020-03-25" -25 11
"2020-03-26" -34 16
"2020-03-27" -35 19
"2020-03-28" -19 13
"2020-03-29" -11 15
"2020-03-30" -42 18
"2020-03-31" -55 28
"2020-04-01" -56 29
"2020-04-02" -54 28
"2020-04-03" -52 30
"2020-04-04" -30 20
"2020-04-05" -14 19
"2020-04-06" -54 29
"2020-04-07" -53 26
"2020-04-08" -54 27
"2020-04-09" -53 28
"2020-04-10" -56 31
"2020-04-11" -27 19
"2020-04-12" -14 19
"2020-04-13" -62 33
"2020-04-14" -53 29
"2020-04-15" -52 29
"2020-04-16" -51 29
"2020-04-17" -49 31
"2020-04-18" -26 21
"2020-04-19" -13 21
"2020-04-20" -52 31
"2020-04-21" -53 30
"2020-04-22" -52 29
"2020-04-23" -51 30
"2020-04-24" -48 32
"2020-04-25" -26 22
"2020-04-26" -13 22
"2020-04-27" -51 31
"2020-04-28" -52 28
"2020-04-29" -51 30
"2020-04-30" -50 30
"2020-05-01" -52 32
"2020-05-02" -21 21
"2020-05-03" -10 21
"2020-05-04" -38 24
"2020-05-05" -41 25
"2020-05-06" -37 23
"2020-05-07" -40 25
"2020-05-08" -34 25
"2020-05-09" -15 17
"2020-05-10"  -7 20
"2020-05-11" -36 23
"2020-05-12" -38 23
"2020-05-13" -34 21
"2020-05-14" -36 21
"2020-05-15" -29 22
"2020-05-16"  -8 15
"2020-05-17"  -2 17
"2020-05-18" -33 22
"2020-05-19" -38 24
"2020-05-20" -33 21
"2020-05-21" -34 22
"2020-05-22" -27 20
"2020-05-23"  -8 15
"2020-05-24"  -2 15
"2020-05-25" -50 26
"2020-05-26" -48 24
"2020-05-27" -29 18
"2020-05-28" -32 22
"2020-05-29" -23 19
"2020-05-30"  -6 14
"2020-05-31"   5 14
"2020-06-01" -26 19
"2020-06-02" -30 19
"2020-06-03" -24 16
"2020-06-04" -25 18
"2020-06-05" -19 17
"2020-06-06"  -2 12
"2020-06-07"   8 13
"2020-06-08" -23 16
"2020-06-09" -26 18
"2020-06-10" -22 16
"2020-06-11" -24 17
"2020-06-12" -34 21
"2020-06-13"   1 12
"2020-06-14"   9 13
"2020-06-15" -22 16
"2020-06-16" -24 16
"2020-06-17" -23 17
"2020-06-18" -27 20
"2020-06-19" -17 15
"2020-06-20"   1 12
"2020-06-21"   9 12
"2020-06-22" -23 17
"2020-06-23" -25 16
"2020-06-24" -22 15
"2020-06-25" -23 16
"2020-06-26" -17 15
"2020-06-27"   0 10
"2020-06-28"   4 13
"2020-06-29" -21 14
"2020-06-30" -24 16
"2020-07-01" -20 14
"2020-07-02" -21 15
"2020-07-03" -15 14
"2020-07-04"   0 11
"2020-07-05"   7 11
"2020-07-06" -20 13
"2020-07-07" -23 15
"2020-07-08" -20 14
"2020-07-09" -21 15
"2020-07-10" -16 14
"2020-07-11"   3  9
"2020-07-12"   7 11
"2020-07-13" -18 13
"2020-07-14" -22 14
"2020-07-15" -19 14
"2020-07-16" -20 15
"2020-07-17" -14 14
"2020-07-18"   4  9
"2020-07-19"   7 11
"2020-07-20" -18 13
"2020-07-21" -21 14
"2020-07-22" -18 13
"2020-07-23" -20 14
"2020-07-24" -14 13
"2020-07-25"   1  9
"2020-07-26"   6 10
"2020-07-27" -19 13
"2020-07-28" -21 13
"2020-07-29" -20 10
"2020-07-30" -41 16
"2020-07-31" -46 18
"2020-08-01" -12  9
"2020-08-02"  -3  9
"2020-08-03" -22 12
"2020-08-04" -22 12
"2020-08-05" -20 12
"2020-08-06" -21 13
"2020-08-07" -12  9
"2020-08-08"   5  5
"2020-08-09"   9  6
"2020-08-10" -15  9
"2020-08-11" -18 10
"2020-08-12" -16  9
"2020-08-13" -17 10
"2020-08-14" -12  9
"2020-08-15"   6  4
"2020-08-16"   9  5
"2020-08-17" -14  8
"2020-08-18" -16  9
"2020-08-19" -14  9
"2020-08-20" -16 10
"2020-08-21" -11 11
"2020-08-22"   6  6
"2020-08-23"  10  6
"2020-08-24" -13 10
"2020-08-25" -16 10
"2020-08-26" -13  9
"2020-08-27" -15 10
"2020-08-28" -11 10
"2020-08-29"   4  6
"2020-08-30"   7  6
"2020-08-31" -13 10
"2020-09-01" -17 11
"2020-09-02" -14 10
"2020-09-03" -15 12
"2020-09-04" -10 12
"2020-09-05"   4  8
"2020-09-06"   6  7
"2020-09-07" -13 11
"2020-09-08" -16 12
"2020-09-09" -16 15
"2020-09-10" -16 14
"2020-09-11" -10 13
"2020-09-12"   2 10
"2020-09-13"   4 10
"2020-09-14" -12 14
"2020-09-15" -17 17
"2020-09-16" -14 15
"2020-09-17" -15 15
"2020-09-18"  -9 14
"2020-09-19"   3 11
"2020-09-20"   4 12
"2020-09-21" -14 15
"2020-09-22" -17 15
"2020-09-23" -16 15
"2020-09-24" -18 17
"2020-09-25" -11 15
"2020-09-26"  -3 12
"2020-09-27"  -3 12
"2020-09-28" -15 16
"2020-09-29" -18 15
"2020-09-30" -20 10
"2020-10-01" -50 13
"2020-10-02" -17  5
"2020-10-03" -13  3
"2020-10-04" -17  2
"2020-10-05" -22  5
"2020-10-06" -25  7
"2020-10-07" -18  6
"2020-10-08" -17 10
"2020-10-09" -10 10
"2020-10-10"  -4  8
"2020-10-11"  -6  8
"2020-10-12" -16 10
"2020-10-13" -19 12
"2020-10-14" -17 13
"2020-10-15" -17 13
"2020-10-16" -12 12
"2020-10-17"  -4  9
"2020-10-18"  -5 10
"2020-10-19" -20 16
"2020-10-20" -32 22
"2020-10-21" -52 29
"2020-10-22" -54 28
"2020-10-23" -51 29
"2020-10-24" -26 16
"2020-10-25" -19 13
"2020-10-26" -29 16
"2020-10-27" -27 13
"2020-10-28" -23 12
"2020-10-29" -45 19
"2020-10-30" -18 11
"2020-10-31" -10  8
"2020-11-01" -12  7
"2020-11-02" -18 10
"2020-11-03" -18  9
"2020-11-04" -17 10
"2020-11-05" -17 10
"2020-11-06" -13 10
"2020-11-07"  -6  7
"2020-11-08"  -9  7
"2020-11-09" -16 10
"2020-11-10" -17 10
"2020-11-11" -16 10
"2020-11-12" -15  9
"2020-11-13" -13  9
"2020-11-14"  -7  5
"2020-11-15" -10  7
"2020-11-16" -15 10
"2020-11-17" -16 10
"2020-11-18" -15 10
"2020-11-19" -14  9
"2020-11-20" -11  9
"2020-11-21"  -5  6
"2020-11-22"  -9  7
"2020-11-23" -13  9
"2020-11-24" -14  9
"2020-11-25" -13  9
"2020-11-26" -13  9
"2020-11-27" -10  8
"2020-11-28"  -5  5
"2020-11-29"  -7  5
"2020-11-30" -11  8
"2020-12-01" -11  8
"2020-12-02" -10  9
"2020-12-03" -10  8
"2020-12-04"  -7  7
"2020-12-05"  -4  5
"2020-12-06"  -5  5
"2020-12-07" -11  8
"2020-12-08" -11  8
"2020-12-09" -11 10
"2020-12-10" -11 12
"2020-12-11"  -9 11
"2020-12-12"  -2  7
"2020-12-13"  -5  9
"2020-12-14" -12 13
"2020-12-15" -13 12
"2020-12-16" -12 12
"2020-12-17" -12 12
"2020-12-18" -11 11
"2020-12-19"  -2  6
"2020-12-20"  -4  8
"2020-12-21" -19 13
"2020-12-22" -22 12
"2020-12-23" -24 13
"2020-12-24" -31 12
"2020-12-25" -65 25
"2020-12-26" -29 13
"2020-12-27" -13 10
"2020-12-28" -53 23
"2020-12-29" -37 17
"2020-12-30" -36 19
"2020-12-31" -38 16
"2021-01-01" -68 29
"2021-01-02" -31 17
"2021-01-03" -10 10
"2021-01-04" -32 19
"2021-01-05" -29 18
"2021-01-06" -25 19
"2021-01-07" -24 18
"2021-01-08" -19 18
"2021-01-09"  -4 11
"2021-01-10"  -2 11
"2021-01-11" -17 16
"2021-01-12" -18 16
"2021-01-13" -18 15
"2021-01-14" -20 15
"2021-01-15" -19 15
"2021-01-16" -10 12
"2021-01-17"  -7 12
"2021-01-18" -19 15
"2021-01-19" -20 14
"2021-01-20" -17 16
"2021-01-21" -17 16
"2021-01-22" -13 15
"2021-01-23"  -3 12
"2021-01-24"  -5 13
"2021-01-25" -15 14
"2021-01-26" -16 14
"2021-01-27" -15 15
"2021-01-28" -15 14
"2021-01-29" -13 11
"2021-01-30"  -8  7
"2021-01-31" -10  8
"2021-02-01" -17 10
"2021-02-02" -18 10
"2021-02-03" -17 11
"2021-02-04" -18 11
"2021-02-05" -15 10
"2021-02-06"  -9  8
"2021-02-07" -12  9
"2021-02-08" -19 10
"2021-02-09" -19 10
"2021-02-10" -17 11
"2021-02-11" -17 11
"2021-02-12" -15  9
"2021-02-13" -11  7
"2021-02-14" -11  7
"2021-02-15" -17 10
"2021-02-16" -18 10
"2021-02-17" -15 10
"2021-02-18" -15 10
"2021-02-19" -13 11
"2021-02-20"  -6  8
"2021-02-21"  -9  9
"2021-02-22" -13 11
"2021-02-23" -13 10
"2021-02-24" -12 11
"2021-02-25" -13 11
"2021-02-26" -11 10
"2021-02-27"  -6  7
"2021-02-28"  -8  7
"2021-03-01" -11  9
"2021-03-02" -12  9
"2021-03-03" -11 10
"2021-03-04" -12 10
"2021-03-05"  -9  9
"2021-03-06"  -5  7
"2021-03-07"  -6  7
"2021-03-08" -10  9
"2021-03-09" -11  9
"2021-03-10" -10 10
"2021-03-11" -10  9
"2021-03-12"  -7  8
"2021-03-13"  -4  6
"2021-03-14"  -6  8
"2021-03-15"  -8  9
"2021-03-16"  -9  9
"2021-03-17"  -7 10
"2021-03-18"  -8 10
"2021-03-19"  -4  9
"2021-03-20"   0  6
"2021-03-21"  -4  7
"2021-03-22"  -7  9
"2021-03-23"  -9  9
"2021-03-24"  -8 10
"2021-03-25"  -7  8
"2021-03-26"  -4  7
"2021-03-27"  -3  5
"2021-03-28"  -5  6
"2021-03-29"  -7  8
end

--------
Thanks in advance!

[Clyde Schechter]

Your question is ambiguous in several ways. Median value of what? Also, for each date, which five weeks? The five weeks preceding? The five weeks following? The five weeks with the date at the center? Some other five weeks related to the date?

In any case, so that you can make some progress while you ponder these questions, the simplest way to solve this is with the -rangestat- command, by Robert Picard, Nick Cox, and Roberto Ferrer, available from SSC. To work with this, and probably for anything else you will want to do with this data, you will need to convert your date variable from string to Stata internal format date variable. The -daily()- function will do that. So it will look something like this:

gen _date = daily(date, "YMD")
assert missing(_date) == missing(date)
format _date %td
drop date
rename _date date

rangestat (p50) variable(s)_whose_median(s)_you_want, interval(date, ???, ???+34)

Everything in italics must be replaced by actual variable(s) names, and numbers that reflect your answers to my questions. Note that you must first calculate the actual value of ???+34 and place that in the third argument of -interval()-. If you write, for example, -interval(date, 1, 1+34)- you will get a syntax error. It has to be -interval(date, 1, 35)-. (This would be the correct way to get a median over a five week period that starts on the day after date and runs for five weeks thereafter.)

[Nick Cox]

I agree with everything Clyde Schechter said and I think he's going to agree with this. The median of 35 daily values is the simplest and in my view best interpretation of what you're asking for.

If and only if you want first a reduction to weekly summaries, and then a median of 5 such, you'll need different code, starting with a definition of weeks.

[Clyde Schechter]

Yes, I do agree with Nick here, as usual! I didn't even notice the additional ambiguity, but, it is, indeed, there.

[Stephen Okiya]

Clyde Schechter am sincerely sorry for lack of clarity in my post. I appreciate you taking time and offering guidance on how I could approach my problem. Thank you!

[Stephen Okiya]

Nick Cox Thanks so much for the additional insights. I sincerely appreciate