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;
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
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.
ballardw,
This is close, but the values aren't showing up quite in the right rows. Thanks.
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 Stats,
This looks like it is working correctly. Thanks for the help!
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.
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.
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
It looks like this works too. Thanks!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.