I have a data set that contains the counts for a vendor in each month for the past year. The count is a representation of how much we use their product in a given month.
I am trying to obtain the total price of what we owe the vendor based on the count multiplied by the price, but I dont know how to calculate this as there is a change in the price based on the amount we use their product.
For the first 150,000 times we use the product, we are charged $0.06. Anything after 150,000, we are charged $0.052500 each time we use the product.
I would like the end result to show the total price for the first 150,000. And then the total price for the remaining portion of the count for each month, including the remaining variables I have listed in the below data set.
Here is a similar post that you might find useful:
My apologies Reeza. Here is the sample data as text.
data WORK.IOVATIONNOW2;
infile datalines dsd truncover;
input Date:YYMMD7. Count:COMMA20. mid:$20. product:$30. vendor:$30. Price:4.2 total_price:32.;
format Date YYMMD7. Count COMMA20. Price 4.2;
datalines;
2018-08 471,190 All Environments RM360 Iovation 0.06 25915.45
2018-06 385,706 All Environments RM360 Iovation 0.06 21213.83
2018-02 348,405 All Environments RM360 Iovation 0.06 19162.275
2018-10 144 All Environments RM360 Iovation 0.06 7.92
2018-05 362,751 All Environments RM360 Iovation 0.06 19951.305
;;;;
here is what I need the data to return:
2018-08 150,000 All Environments RM360 Iovation 0.06 9000
2018-08 321,190 All Environments RM360 Iovation 0.0525 16862.475
2018-06 150,000 All Environments RM360 Iovation 0.06 9000
2018-06 235,706 All Environments RM360 Iovation 0.0525 12374.565
2018-07 150,000 All Environments RM360 Iovation 0.06 9000
2018-07 198,405 All Environments RM360 Iovation 0.0525 10415.26
2018-05 150,000 All Environments RM360 Iovation 0.06 9000
2018-05 250,000 All Environments RM360 Iovation 0.06 13125.00
Count seems to be expressed in thousands, so you need
price150000 = min(count, 150)*1000*0.06;
totalPrice = max(0, count - 150)*1000*0.0525 + price150000;
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 16. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.