DATA Step, Macro, Functions and more

How to apportion data over a specified date span

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

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: 19,849

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;

View solution in original post


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

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: 19,849

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: 11,343

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 61 views
  • 0 likes
  • 3 in conversation