Solved
Contributor
Posts: 26

# 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;

Accepted Solutions
Solution
‎05-29-2014 03:55 PM
Posts: 5,521

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

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

All Replies
Super User
Posts: 13,507

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

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
Posts: 5,521

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

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: 23,677

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

Specify Time as Time Variable

Specify Sales as Variable

Specify Customer and Region as Group BY

Click on Transformations

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,766

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

```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.