topic How to apportion data over a specified date span in SAS Programming

How to apportion data over a specified date span

nirajp91 — Wed, 26 Jul 2017 16:00:56 GMT

I am looking for a solution for the following:

I have a dataset as follows

ID Pass Type Transaction_Amount Start_date End_date

1 30D $30 01072016 31072016

2 30D $30 03072016 02082016

3 7D $14 02072016 08072016

I would like to have a dataset where for each of the ID, I can spread the the transaction amount over the start date and end date. For example, if ID=1 purchased a 30D pass on the 3/7/2016, I would like to distribute the transaction_amount which is $30 over the the validity of the 30D pass which is denoted by the start_date and the end_date.

The final table would have the data above + columns for dates (starting with the earliest start_date in the dataset) and within each date column there should be the value of the pass for each day which in the case for ID=1 would be $30/30 = $1 and for the case of ID=3 would be $14/7=$2 for each day beginning from the start_date all the way to the end_date.

This is what i'm seeking

ID Pass Type Transaction_Amount 01072016 02072016 03072016 .......... 31072016 01082016 02082016

1 30D $30 1 1 1 1

2 30D $30 1 1 1 1

3 7D $14 2 2

Re: How to apportion data over a specified date span

Reeza — Wed, 26 Jul 2017 15:50:23 GMT

Your code below does not work.

You can subtract the dates to determine the number of days, and then use a do loop to create a record for each date.

average_amount = amount / (end_date - start_date + 1);
do date=start_date to end_date;
output;
end;

Re: How to apportion data over a specified date span

nirajp91 — Wed, 26 Jul 2017 16:01:48 GMT

Thank you for your reply, I have updated what the dataset looks like currently and what I'd like to achieve. Appreciate the help.

Re: How to apportion data over a specified date span

Reeza — Wed, 26 Jul 2017 16:12:06 GMT

I would do it the way I suggested and then use PROC TRANSPOSE to get the wide data set.

Wide data like that is rarely useful. And realize you're going to have a lot of empty cells and a very wide dataset since you'll need a date variable for any date in any interval in your dataset.

Re: How to apportion data over a specified date span

ballardw — Wed, 26 Jul 2017 17:17:25 GMT

This may seem a silly question but since you display a D at the end of the PassType variable are there other possibilities such a 1M (month) or yearly?

If so then you'll need to do a bit more to get your daily average calculation.

And what if you have data such as

3 7D $14 02072016 28072016

which might indicate a less than daily transaction frequency. Do you actually want values of less than one per day? Of course if this can't happen then no worries.