How to apportion data over a specified date span

[nirajp91]

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

[Reeza · Accepted solution]

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;

[nirajp91]

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

[Reeza]

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.

[ballardw]

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.