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
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;
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;
Thank you for your reply, I have updated what the dataset looks like currently and what I'd like to achieve. Appreciate the help.
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.