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: 17,963

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

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: 17,963

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

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: 17,963

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

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: 17,963

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

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
  • 907 views
  • 1 like
  • 2 in conversation