Solved
New Contributor
Posts: 3

# How to apportion data over a specified date span

[ Edited ]

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

Accepted Solutions
Solution
‎07-27-2017 12:04 AM
Super User
Posts: 23,662

## Re: How to apportion data over a specified date span

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

All Replies
Solution
‎07-27-2017 12:04 AM
Super User
Posts: 23,662

## Re: How to apportion data over a specified date span

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;``````
New Contributor
Posts: 3

## Re: How to apportion data over a specified date span

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

Super User
Posts: 23,662

## Re: How to apportion data over a specified date span

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.

Super User
Posts: 13,498

## Re: How to apportion data over a specified date span

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.

☑ This topic is solved.