Calculation of average inter-arrival time with time-series data using SAS

Accepted Solution Solved
Reply
Contributor
Posts: 26
Accepted Solution

Calculation of average inter-arrival time with time-series data using SAS

I'm trying to calculate a value, let's call it lambda, that is an average of the inter-arrival times of sales within each customer in each region in my time-series dataset.  I am using SAS Enterprise Guide.  Here is an example of how I'm trying to calculate this value:

photo.JPG

Here is a sample of a smaller dataset that is similar to my larger one that I've been working on:

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 0

  1 2 2004Q2 2004 0

  1 2 2004Q3 2004 6

  1 2 2004Q4 2004 0

  1 2 2005Q1 2005 2

  1 2 2005Q2 2005 0

  1 2 2005Q3 2005 3

  1 2 2005Q4 2005 0

  2 6 2003Q1 2003 0

  2 6 2003Q2 2003 0

  2 6 2003Q3 2003 3

  2 6 2003Q4 2003 5

  2 6 2004Q1 2004 0

  2 6 2004Q2 2004 0

  2 6 2004Q3 2004 0

  2 6 2004Q4 2004 2

  2 6 2005Q1 2005 0

  2 6 2005Q2 2005 4

  2 6 2005Q3 2005 0

  2 6 2005Q4 2005 0

  ;

run;

proc sort data=sample;

  by customer region;

run;

I've attached an image below that shows the method I've been attempting and what the output would look like if I could get the correct results, but I'm not sure how to get this done in SAS.  I am open to any suggestions if there is an easier or more effective method.

Anyway, I've been trying to create a variable count_sales that counts the number of sales within each customer and region. Then I was trying to calculate a variable count_gap that would count the number of quarters between sales (starting at the first sale). I was then going to have a variable sum_gaps that would keep track of the sum of these inter-arrival times (gaps between sales), so it would basically add the value of count_gap at each sale.  I then thought I could calculate lambda by taking sum_gaps and dividing that by (count_sales - 1).  I'm not sure if this is the most effective method, but it's all I could come up with at the time, and I'm still not sure how to do the calculations in SAS. Any help is greatly appreciated.

Capture.PNG


Accepted Solutions
Solution
‎06-03-2014 03:06 PM
Super User
Posts: 19,800

Re: Calculation of average inter-arrival time with time-series data using SAS

Posted in reply to klappy711

Incredibly inelegant but this matches your cases.

I'm not sure it will match all cases so you'll need to check it.

data part1;

    set sample;

    by customer region;

    retain count_sales;

    if first.region then count_sales=0; 

   

    if sales ne 0 then count_sales+1;

    sales_indicator=sales ne 0;

   

run;

data part2;

    set part1;

    by customer region sales_indicator notsorted;

    retain first_flag count_gap sum_gaps;

    lag_sales=lag(sales);

    if first.region then do;

        first_flag=0; count_gap=0; lag_sales=0; sum_gaps=0;

    end;

    if first_flag=1 then do;

        if lag_sales=0 then  count_gap=count_gap+1;

        else count_gap=1;

    end;

    if first.sales_indicator=1 and sales ne 0 then first_flag=1;

    if last.sales_indicator and sales_indicator=1 then sum_gaps=sum_gaps+count_gap;

    if last.region then lambda=sum_gaps/(count_sales-1);

    drop lag_sales;

run;

View solution in original post


All Replies
Super User
Posts: 19,800

Re: Calculation of average inter-arrival time with time-series data using SAS

Posted in reply to klappy711

For the first one why is the sum_gaps 8 not 7, I count 5 gap periods with 7 zeros in total.

Contributor
Posts: 26

Re: Calculation of average inter-arrival time with time-series data using SAS

I'm only looking at the gaps between the first and last sale within each customer, so since customer 1 has 5 sales, there will be 4 gaps between those sales.  So, sum gaps will not actually just be the number of zeros. For instance, when there are sales in back to back quarters, count_gap should be one (the number of quarters between sales).

Super User
Posts: 19,800

Re: Calculation of average inter-arrival time with time-series data using SAS

Posted in reply to klappy711

Well assuming you have all you need in the other columns, I guess I'd recommend using a data step - this is just outside what I'd consider easily doable in EG.

data want;

set have;

by customer;

if last.customer then lambda=sum_gaps/count_sales-1;

run;

And then merge the result from this back into the table.

Contributor
Posts: 26

Re: Calculation of average inter-arrival time with time-series data using SAS

Reeza,

I actually don't have the count_sales, count_gap, and sum_gap variables. Those are what I'm having trouble calculating.

Solution
‎06-03-2014 03:06 PM
Super User
Posts: 19,800

Re: Calculation of average inter-arrival time with time-series data using SAS

Posted in reply to klappy711

Incredibly inelegant but this matches your cases.

I'm not sure it will match all cases so you'll need to check it.

data part1;

    set sample;

    by customer region;

    retain count_sales;

    if first.region then count_sales=0; 

   

    if sales ne 0 then count_sales+1;

    sales_indicator=sales ne 0;

   

run;

data part2;

    set part1;

    by customer region sales_indicator notsorted;

    retain first_flag count_gap sum_gaps;

    lag_sales=lag(sales);

    if first.region then do;

        first_flag=0; count_gap=0; lag_sales=0; sum_gaps=0;

    end;

    if first_flag=1 then do;

        if lag_sales=0 then  count_gap=count_gap+1;

        else count_gap=1;

    end;

    if first.sales_indicator=1 and sales ne 0 then first_flag=1;

    if last.sales_indicator and sales_indicator=1 then sum_gaps=sum_gaps+count_gap;

    if last.region then lambda=sum_gaps/(count_sales-1);

    drop lag_sales;

run;

Contributor
Posts: 26

Re: Calculation of average inter-arrival time with time-series data using SAS

Reeza,

This appears to work. Thanks!

🔒 This topic is solved and locked.

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

Discussion stats
  • 6 replies
  • 1018 views
  • 1 like
  • 2 in conversation