topic Re: Calculation based on Count in New SAS User

Calculation based on Count

aperansi — Tue, 27 Nov 2018 20:15:00 GMT

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.

Re: Calculation based on Count

pink_poodle — Mon, 29 Oct 2018 21:33:54 GMT

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/506531#M1344

Re: Calculation based on Count

Reeza — Mon, 29 Oct 2018 21:44:36 GMT

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

Re: Calculation based on Count

Reeza — Mon, 29 Oct 2018 21:44:56 GMT

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

Re: Calculation based on Count

PGStats — Tue, 30 Oct 2018 05:14:25 GMT

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;

Re: Calculation based on Count

aperansi — Tue, 30 Oct 2018 13:41:33 GMT

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

Re: Calculation based on Count

Reeza — Tue, 30 Oct 2018 15:03:47 GMT

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.

Re: Calculation based on Count

aperansi — Tue, 30 Oct 2018 15:39:15 GMT

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 😕