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

Accepted Solution Solved
Reply
Contributor
Posts: 26
Accepted Solution

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

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;

Capture.PNG


Accepted Solutions
Solution
‎05-29-2014 03:55 PM
Respected Advisor
Posts: 4,935

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

Posted in reply to klappy711

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

PG

View solution in original post


All Replies
Super User
Posts: 11,343

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

Posted in reply to klappy711

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.

Contributor
Posts: 26

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

ballardw,

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

Solution
‎05-29-2014 03:55 PM
Respected Advisor
Posts: 4,935

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

Posted in reply to klappy711

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

PG
Contributor
Posts: 26

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

PG Stats,

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

Super User
Posts: 19,872

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

Posted in reply to klappy711

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.

Contributor
Posts: 26

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

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.

Super User
Posts: 10,046

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

Posted in reply to klappy711
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

Contributor
Posts: 26

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

It looks like this works too. Thanks!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 556 views
  • 3 likes
  • 5 in conversation