Adding and simplifying observations; from daily to weekly

[Fabrizio Ugarte]

Hello to all.
I´m kind of new in using STATA, and I´m having trouble figuring out how to merge observations. I have binary values for several variables, with several observations per day, and I have to present the weekly values (the addition of them all, per week). I understand it´s a bit basic, but I would appreciate any help. Here´s an example:
Date Week CO MU MN
2017-03-18 17/11 0 0 1
2017-03-18 17/11 0 0 1
2017-03-18 17/11 0 1 0
2017-03-18 17/11 0 0 1
2017-03-18 17/11 0 0 1
2017-03-18 17/11 0 0 1
2017-03-18 17/11 0 0 1
2017-03-19 17/12 0 0 1
2017-03-19 17/12 0 0 1
2017-03-19 17/12 0 0 1
2017-03-19 17/12 0 0 1
2017-03-19 17/12 0 0 1
2017-03-19 17/12 0 0 1
2017-03-19 17/12 0 0 1
2017-03-19 17/12 0 0 1
2017-03-19 17/12 0 1 0

[Nick Cox]

That is not a data example in Statalist's sense, but assuming that Date is a Stata daily date variable and the definition of a week is that it starts on a Sunday, then

gen WDate = Date - dow(Date)
format WDate %td 

egen CO_mean = mean(CO), by(WDate)

Your existing Week variable may or may not be helpful here.

[Clyde Schechter]

* Example generated by -dataex-. For more info, type help dataex
clear
input str10 date str5 week byte(co mu mn)
"2017-03-18" "17/11" 0 0 1
"2017-03-18" "17/11" 0 0 1
"2017-03-18" "17/11" 0 1 0
"2017-03-18" "17/11" 0 0 1
"2017-03-18" "17/11" 0 0 1
"2017-03-18" "17/11" 0 0 1
"2017-03-18" "17/11" 0 0 1
"2017-03-19" "17/12" 0 0 1
"2017-03-19" "17/12" 0 0 1
"2017-03-19" "17/12" 0 0 1
"2017-03-19" "17/12" 0 0 1
"2017-03-19" "17/12" 0 0 1
"2017-03-19" "17/12" 0 0 1
"2017-03-19" "17/12" 0 0 1
"2017-03-19" "17/12" 0 0 1
"2017-03-19" "17/12" 0 1 0
end

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

foreach v of varlist co mu mn {
    by week (date), sort: egen `v'_total = total(`v')
}

The actual hard part is defining what is mean by a week, but you have already resolved that thorny issue in your data, so the rest is just some easy Stata code.

Note that the conversion of the date variable from string to actual numeric Stata internal format date variable is not necessary for the solution of this particular problem. But dates as strings are close to useless for the purposes of computation, so I urge you to make this conversion anyway for the sake of whatever else you are going to do with this data.

By the way, if what you wanted to do is reduce this data set to one with a single observation for each week, containing the week totals of co mu and mn, the code is even simpler:

collapse (sum) co mu mn, by(week)

In the future, when showing data examples, please use the -dataex- command to do so, as I have here. If you are running version 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

Added: Crossed with #2. The advice there is different and takes a skeptical view of your definition of week. That is fair enough. There are different ways to define a week, each with its attendant difficulties. By the way, where Nick says -format Wdate- I think he meant -format Wdate %td-. He also assumes that your Date variable is already a Stata internal format daily date variable and that this is just not apparent in your post because you did not use -dataex-.