BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
klappy711
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

8 REPLIES 8
ballardw
Super User

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
Calcite | Level 5

ballardw,

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

PGStats
Opal | Level 21

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
klappy711
Calcite | Level 5

PG Stats,

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

Reeza
Super User

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
Calcite | Level 5

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
Super User
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
Calcite | Level 5

It looks like this works too. Thanks!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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