Calculating 6 quarter percent change with time-series data in SAS

[klappy711]

I'm trying to calculate 6-quarter and 10-quarter percent change of sales values for my time-series data. These values need to be calculated for sales within each customer and each region. For example, for 6-qtr percent change for the observation from 2004Q2, I would calculate the percent change as pctchange = [(sales in 2004Q2) - (sales in 2003Q1)] / (sales in 2003Q1). This value would then be multiplied by 100 to be shown as a percent. The values before this date would be empty since there are not 6 full quarters of sales to calculate from. So, another example, 6-qtr percent change for the value at 2005Q3 would be pctchange = [(sales in 2005Q4) - (sales in 2004Q3)] / (sales in 2004Q3) then multiplied by 100.  The 10-qtr percent change values would be calculated the same way, only with a width of 10 quarters instead of 6.

I was trying to mess with coding some loops, but I can't seem to figure out a way to do this. I'm using SAS Enterprise Guide.

Here is a sample subset of my data:

data sample;

  input customer region qtr year sales;

  informat qtr yyq6.;

  format qtr yyq6.;

  datalines;

  1 2 2003Q1 2003 0

  1 2 2003Q2 2003 0

  1 2 2003Q3 2003 3

  1 2 2003Q4 2003 5

  1 2 2004Q1 2004 3

  1 2 2004Q2 2004 0

  1 2 2004Q3 2004 6

  1 2 2004Q4 2004 2

  1 2 2005Q1 2005 0

  1 2 2005Q2 2005 4

  1 2 2005Q3 2005 0

  1 2 2005Q4 2005 2

  2 6 2003Q1 2003 0

  2 6 2003Q2 2003 0

  2 6 2003Q3 2003 3

  2 6 2003Q4 2003 5

  2 6 2004Q1 2004 3

  2 6 2004Q2 2004 0

  2 6 2004Q3 2004 6

  2 6 2004Q4 2004 2

  2 6 2005Q1 2005 0

  2 6 2005Q2 2005 4

  2 6 2005Q3 2005 0

  2 6 2005Q4 2005 2

  ;

run;

[ballardw]

Does this meet your need:

proc sql;

     create table SixQtr as

     select a.*, b.sales as SixQtrSale,(100*(b.sales - a.sales)/a.sales) as SixQtrChange

     from sample as a left join sample as b  

     on a.customer =b.customer and a.region=b.region and a.qtr = intnx('qtr',b.qtr,-6);

quit;

Modify Six to 10 for the other, if so.

[klappy711]

ballardw,

This is close, but the values aren't showing up quite in the right rows. Thanks.

[PGStats · Accepted solution]

Replace 6 by 10 for the 10 QTR lag :

proc sql;

create table sample6 as

select

    s1.*,

    (s1.sales/s2.sales)-1 as change format=percentn10.1

from

    sample as s1 left join

    sample as s2

        on s1.customer=s2.customer and s1.region=s2.region and

            s1.qtr = intnx("QTR", s2.qtr, 6)

order by customer, region, qtr;

quit;

PG

[klappy711]

PG Stats,

This looks like it is working correctly. Thanks for the help!

[Reeza]

Tasks>Time Series>Prepare Time Series Data

Specify Time as Time Variable

Specify Sales as Variable

Specify Customer and Region as Group BY

Click on Transformations

Click on Add

Add Lag

Click on Lag and specify 6
Click on Lag and specify 10

Then use Query Builder calculate percent change.

You may be able to specify a percent change directly in the transformations but I couldn't understand them!

FYI The time series select transformation is the worst GUI screen I have ever seen.

[klappy711]

Reeza,

I couldn't quite get this to do what I wanted. Thanks for the help though. I was hoping there would be a way to do it using the GUI. I see what you mean by the time series select transformation being one of the worst GUI's.

[Ksharp]

data have;
  input customer region qtr year sales;
  informat qtr yyq6.;
  format qtr yyq6.;
  datalines;
  1 2 2003Q1 2003 0
  1 2 2003Q2 2003 0
  1 2 2003Q3 2003 3
  1 2 2003Q4 2003 5
  1 2 2004Q1 2004 3
  1 2 2004Q2 2004 0
  1 2 2004Q3 2004 6
  1 2 2004Q4 2004 2
  1 2 2005Q1 2005 0
  1 2 2005Q2 2005 4
  1 2 2005Q3 2005 0
  1 2 2005Q4 2005 2
  2 6 2003Q1 2003 0
  2 6 2003Q2 2003 0
  2 6 2003Q3 2003 3
  2 6 2003Q4 2003 5
  2 6 2004Q1 2004 3
  2 6 2004Q2 2004 0
  2 6 2004Q3 2004 6
  2 6 2004Q4 2004 2
  2 6 2005Q1 2005 0
  2 6 2005Q2 2005 4
  2 6 2005Q3 2005 0
  2 6 2005Q4 2005 2
  ;
run;
proc sql;
create table want as
 select *,(a.sales-(select sales from have where  customer=a.customer and  region=a.region and qtr=intnx('qtr',a.qtr,-6)))/(select sales from have where  customer=a.customer and  region=a.region and qtr=intnx('qtr',a.qtr,-6)) as percent format=percentn8.2
  from have as a;
quit;

Xia Keshan

[klappy711]

It looks like this works too. Thanks!