Calculation based on Count

[aperansi]

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. 

[pink_poodle]

Here is a similar post that you might find useful:

https://communities.sas.com/t5/New-SAS-User/If-statement-to-calculate-price-based-on-count/m-p/50653...

[Reeza]

Post sample data as text, not an image and the expected output as much as possible aligned to the example data.

[aperansi]

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

[Reeza · Accepted solution]

Your code doesn't run for me 😞

The solution is something along the lines of :

if count>150000 then do;
remainder=total_price - 9000;
total_price=9000 ; output;
total_price=remainder; price=0.0525; output;
end;

Personally, it's probably easier if the data was in the same row.

[aperansi]

I copied the code directly from the log so not sure why it isnt working:(

Thank you for your help Reeza. I agree it would be simpler, but the requester is insistent that they see the breakout 😕

[Reeza]

Instructions on posting sample data is here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

[PGStats]

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;