Solved
Contributor
Posts: 26

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

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.

Accepted Solutions
Solution
‎06-03-2014 03:06 PM
Super User
Posts: 23,700

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

All Replies
Super User
Posts: 23,700

## 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: 23,700

## 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: 23,700

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