Sum of a variable

[ballardw]

---

@BayzidR wrote:

I want to run regression model on the newly generated variable. I will create multiple variables with different resolutions (eg., weekly total by participants, monthly total by participants etc ) and run separate models on them. I have daily data per participant.

---

But you did not request an "aggregate data set". You requested add a value to all records of an existing data set.

IF you have a variable to indicate the DATE (if you don't there is something wrong) of the daily data then you can create summaries by calendar month by using the proper format in Proc Summary to aggregate the data.

A large data set should be sorted by the "grouping" variables to use the following code.

Example:

data example;
   do id=1,2,3;
       do date= '01Jan1999'd to '23OCT2023'd;
         somevalue = rand('integer',1000);
         output;
       end;
   end;
   format date date9.;
run;

/* proc sort by id date to make sure your data is in order*/
 
proc summary data=example;
   by id date;
   format date Monyy7.;
   var somevalue ;
   output out=monthsummary (drop=_type_) sum=;
run;

The output data set will contain a variable _freq_ with the count of observations used. If you feel a need can rename the sum variable whatever you want, the above uses the same variable name as the Var. Which may be useful so you don't have to change names of variables in the various regressions just use the correct input set.

to create calendar weekly one way:

 
proc summary data=example;
   by id date;
   format date weeku6.;
   var somevalue ;
   output out=weeksummary (drop=_type_) sum=;
run;

Week appearance with the above will be a 2-digit year followed by W then the number of the week in the year. There are 3 related formats, WeekU WeekV and WeekW that have differences on when a "week" starts and how the transition across years works.

If you Print the data with a DATE9 or similar format assigned the DATE in the result you should get the first day of the "week" as the value.

Formats can create groups of any variable and will be honored by procedures like Summary, Freq and most analysis, reporting or graphing procedures (caution with custom date formats and graphing may not always be as expected).