Adjusting variables in a timeseries project

[vfarmak]

Hi everyone, 

I have in a timeseries project two variables: one with a weekly frequency that the data are reported in the beginning of the month and another that is quarterly and is being reported in the end of the month.

What I want to do is that I want to adjust the weekly variable to the quarter variable. 

I am using SAS Econometrics and Forecasting module on Viya.

I have read through the documentation (ETS User's Guide) in the section "Specifying Observation Characteristics" and I saw this example:

proc expand data=annual out=monthly
from=year to=month;
id date;
convert x y z;
convert a b c / observed=total;
run;

However I have noticed the observed options and it provides the "end" keyword, on how to construct the interpolation of the series. 

So what I try to do is the following:

proc expand data=wkl out=wkl_t from=week to=quarter;
 id pDate;
convert claims = claims_q / transformout=(sum) observed=end;
run;

Am I heading in the right direction?

Best Regards,

Vasileios

[sbxkoenk]

You are not (entirely) heading in the right direction.

You do not need transformout=(sum) which is needed if you want a cumulative sum.

See this PROC EXPAND documentation page where they provide two ways to convert from a higher frequency to a lower frequency.

SAS Help Center: Aggregating to Lower Frequency Series

Note you can only use the first way discussed because the second one does not allow conversion from non-nested intervals, such as converting from weekly to monthly or to quarterly values.

The FROM=WEEK and TO=QUARTER (QTR) option values specify non-nested intervals. Remark that an ID variable must be used in PROC EXPAND for conversion between irregular intervals.

But I have to admit my PROC EXPAND example that I was preparing for you does not work as expected (and I don't have time to explore this further). 🙄 🤔 ☹️ 😢
I will send you a PROC TIMESERIES solution.

Ciao,

Koen

[sbxkoenk · Accepted solution]

I believe this is what you want. Correct?

data weekly;
 date='03AUG2025'd; claims =7;   output;
 date='10AUG2025'd; claims =20;  output;
 date='07DEC2025'd; claims =7;   output;
 date='21DEC2025'd; claims =20;  output;
 format date date7.;
run;

proc timeseries data=weekly out=quarterly;
   id date interval=quarter
           accumulate=total
           setmiss=0;
   var claims;
run;

data quarterly;
 set quarterly(rename=(claims=claims_q));
 date_end_quarter = INTNX('QTR',date,1) - 1;
 format date_end_quarter date7.;
 drop date;
run;
/* end of program */

Ciao,

Koen

[vfarmak]

Thank you @sbxkoenk very much for the solution provided.

I didn't know that proc timeseries was used also used for changing frequencies.

I had the impression that only visualizes the timeseries.

I will test it and I will let you know.

[sbxkoenk]

Strictly speaking PROC TIMESERIES is not for changing frequencies, 

but it can be used for accumulating Transactional Data into Time Series Data (like quarterly data).

Your source data is not transactional -- it is already a time series with a fixed-width interval (equally spaced) being weekly -- , but PROC TIMESERIES couldn't care less.

[EDIT]
Adding an example from the documentation.
SAS Help Center: Accumulating Transactional Data into Time Series Data

Ciao,

Koen

[sbxkoenk]

This also works :

data weekly;
 date='03AUG2025'd; claims =7;   output;
 date='10AUG2025'd; claims =20;  output;
 date='07DEC2025'd; claims =7;   output;
 date='21DEC2025'd; claims =20;  output;
 format date date7.;
run;

proc means data=weekly SUM nway noprint;
  CLASS date;
  VAR claims;
  output out=quarterly sum=claims_q;
  format date YYQ.;
run;

data quarterly;
 set quarterly;
 date_end_quarter = INTNX('QTR',date,1) - 1;
 format date_end_quarter date7.;
 drop date;
run;
/* end of program */

But be careful, because just like PROC TIMESERIES, PROC MEANS does not realize that the original data has a weekly interval. The above PROC MEANS only looks at the date (day!) and which quarter it belongs to (in the year).
So if you take the last day of the week to contain your weekly total instead of the first day, you will probably get a different result. This is because the first day of the week may fall in one quarter and the last day of the week in the following quarter.

Ciao,

Koen

[vfarmak]

Yes I think this is the one. 

Basically the first date of the month contains the data from the previous month (as it is reported in the beginning of the period).