Collapse the uneven multiple daily data to average daily data in each quarter

[saurabh_latrobe]

Dear Friends,

I am quite new to SAS. I am struggling to write a code in SAS.

Objective - Collapse the daily data on return into average of daily return in each quarter end date like 31-Mar-2001, 30-Jun-2001,30-Sep-2001..... for each company.

Other Details - There are multiple returns in a single date for a company and there will be many dates which will not be there in the dataset. The program should take the average of all these returns for each company and in the output datafile, it should show only the quarter end dates with the average (daily) return in that quarter. The data runs into millions so it will be difficult to share (sample is in the table below). In the output, I need these 3 columns but dates should be only the end date of each quarter as shown in the output table below:

Required Output Table:

Date	Company Id	Return (Average of all Daily Returns across the given quarter)
31/03/2001	xyz	2%
30/06/2001	xyz	1.38%
30/09/2001	xyz	1.29%
31/12/2001	xyz	3.01%
....	....	...
31/03/2010	xyz	1.45%
30/06/2010	xyz	3%
30/09/2010	xyz	2.12%
31/12/2010	xyz	2.07%
....
31/03/2001	abc	0.99%
30/06/2001	abc	1.75%
30/09/2001	abc	1.67%
31/12/2001	abc	2.01%
...	....
31/03/2011	abc	3%
30/06/2011	abc	1.23%
30/09/2011	abc	2%
31/12/2011	abc	1.3%
...	...	...

Input Data Sample -

Date	Company Id	Daily Return
01/01/2001	abc	1.1%
01/01/2001	abc	1.23%
01/01/2001	abc	0.67%
02/01/2001	abc	.89%
02/01/2001	abc	1.02%
05/02/2001	abc	1%
06/02/2001	abc	0.99%
06/02/2001	abc	2%
......	...	....
23/07/2010	abc	1.34%
.....
10/01/2003	xyz	2.3%
10/01/2003	xyz	1.87%
...	...	...
28/06/2009	xyz	3%
02/07/2009	xyz	1.2%
02/07/2009	xyz	0.59%
.....	...	...
30/09/2009	xyz	1.23%
04/10/2009	xyz	2%

[ballardw · Accepted solution]

First the relatively easy part. When you have an actual SAS date value you can create groups that SAS procedures will recognize by using a Format. So if you use a format such as YYQ6 with a date value in proc means or summary you create calendar groups.

I don't have your data and this may not work if you have enough combinations of date and company:

Proc summary data=have nway;

   class date company;

   format date yyq6.;

   var return;

   output out=want (drop= _freq_ _type_) mean =;

run;

You would need a pass through a data step to ensure the date value was the end of the quarter as this approach tends to keep the earliest value actually encountered in the data for the group.

Alternatively you could create a new variable that holds the desired value and then summarize:

Data need;
    set have;
    endofquarter = intnx('quarter', date,0,'E');
    format endofquarter ddmmyy10.;
run;
proc sort data=need;
   by endofquarter company;
run;

proc summary data=need;
   by endofquarter company;
   var return;
   output out=want  mean=;
run;

Now for the bad news: averaging daily percentages is very likely not going to yield a true percentage unless the number of values is exactly the same for every single day of record per company.

An easy example of one rate and why you may want to reconsider, or redefine, the problem.

Suppose I drive a car and for 1 mile I am costing downhill and on only use 0.01 gallons of gas, the mpg=100. Then I drive the next 100 miles and use 5 gallons or mpg=20. So would the average mpg across that tripe be the mean (100+20)/2 = 60?

[saurabh_latrobe]

Thanks a lot for the solution. I agree on your issue of average.