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

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

klappy711 — Thu, 29 May 2014 17:51:49 GMT

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;

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

ballardw — Thu, 29 May 2014 19:38:19 GMT

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.

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

PGStats — Thu, 29 May 2014 19:55:18 GMT

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;

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

Reeza — Thu, 29 May 2014 20:02:38 GMT

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.

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

klappy711 — Fri, 30 May 2014 12:18:33 GMT

ballardw,

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

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

klappy711 — Fri, 30 May 2014 12:19:11 GMT

PG Stats,

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

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

klappy711 — Fri, 30 May 2014 12:21:19 GMT

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.

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

Ksharp — Fri, 30 May 2014 13:30:15 GMT

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

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

klappy711 — Fri, 30 May 2014 13:41:33 GMT

It looks like this works too. Thanks!