If statement to calculate price based on count

[aperansi]

I have the data set below in which I am trying to set a different price based on the count column. For the product Bank Account Verify, anytime the count goes over 25k, I need the program to input the price of .63 cents. My If statement below does that, however I still have an issue.

The way the product works is we are billed for the first 25k at one rate. Anything over 25k, we are then billed at a smaller rate. I want the program to still have a price of .73 cents for the first 25k count, and anything over that will be priced at the .63 cent rate. 

The final result should show two different observations on the data set with the two different prices based on the counts.

Please provide any recommendations you have, and thank you for taking the time to assist.

[ewv]

data Ryan.MicroBiltDiscount; 
set Vendor_Final_Price;
Price = .73; 
if Count > 25000 and product = 'Bank Account Verify' then do;
	Price = .63;
	Count = Count-25000;
	output;
	Price = .73;
	Count =25000;
	output;
end;
run;

Try this out and let me know if it works. If It does, I can explain some of the rationale behind it

[aperansi]

This did work! The only problem now is that the rest of the products that I have coming into the MICROBILTDISCOUNT data set didnt pull in. The other products dont need this discount applied, but I just wanted them all to pull in to make it easier for the next datastep.

[ewv · Accepted solution]

data Ryan.MicroBiltDiscount; set Vendor_Final_Price;
Price = .73; if Count > 25000 and product = 'Bank Account Verify' then do;
Price = .63;
Count = Count-25000;
output;
Price = .73;
Count =25000;
output;
end;
else output;
run;


I added the line 'else output;' on the second to last line. Let me know if that solves it

[aperansi]

So that worked! However, now my only problem is that the price for all of the other products are showing up as .73 cents when they arent that price. I appreciate all your help with this.

[ewv]

Is price also a variable from the Vendor_Final_price table? If so, I think you could just remove the first instance of 'Price = .73;' outside of if statements.

[aperansi]

Yeah, its a variable in the Vendor_Final_Price table. I tried that and it worked! Thank you again!

Would you mind explaining the rationale behind the code so that I have an understanding to apply this later down the line?

[ewv]

Absolutely. So in a SAS data step program, there is an implicit "OUTPUT" command for each time it loops through the data step

So in your original code...
data Ryan.MicroBiltDiscount; set Vendor_Final_Price; if Count > 25000 and product = 'Bank Account Verify' then Price = .63; run;
... each loop through the data set it would evaluate the if condition, change price if the if condition was true, and the OUTPUT

In my adjustments...
data Ryan.MicroBiltDiscount; set Vendor_Final_Price;
if Count > 25000 and product = 'Bank Account Verify' then do;
Price = .63;
Count = Count-25000;
output;
Price = .73;
Count =25000;
output;
end;
else output;
run;

.. I have 3 explicit OUTPUT statements. Each output statement will create a new row in your output data set. So when the if condition is true (Count > 25000 and product = 'Bank Account Verify' ) it outputs two rows as required by your needs. However, when the if condition is false, it runs the else statement and outputs only one row, without making any data changes (which is why I had you remove the first instance of "price = .73;" because that wasn't in the IF statement and was always going to be run even when you didn't want to change the price.

Now I mentioned in the beginning of this that there is an implicit OUTPUT at the end of a datastep program. However, if you have any explicit OUTPUT statements, it will not include this implicit output. This is why I included the "else output" statement, and why you weren't seeing any of your other product codes with my first code draft.

Hope that help!

[ewv]

Glad to help! Been working on my SAS certifications so this is good practice. We'll solve it one piece at a time 🙂

[ballardw]

 INPUT and desired OUTPUT for given input help. It isn't quite clear what "The final result should show two different observations on the data set with the two different prices based on the counts." actually means in this context. Show some output.

Also you say: ". My If statement below does that, however I still have an issue". So, What is the issue? Incorrect value on some records, all records,

[aperansi]

With the code I have, I cant show you what I want the output I want looks like, but I made an example in Excel for you to see.

The total count for the Bank Account Verify Product in the month of September is 62,309.

The first observation has a count of 25k, and the price is .73 cents. The next observations count has whats left of the total count for the Bank Account Verify product, and It has a price of .63 cents. Hope this makes sense. 

Currently, the issue I am having is with the current if statement.  It is assigning the price of .63 cents to the Bank Account Verify product when the count is over 25k. I need it to break out the first 25k at .73 cents, then the remaining of the count at .63 cents. 

Date

vendor

mid

product

Count

Price

2018-09	MicroBilt	Online	Bank Account Verify	25,000	0.73
2018-09	MicroBilt	Online	Bank Account Verify	37,309	0.63

[pink_poodle]

data subset;
set yourdata;
where count > 25000;
run;

proc sort data = subset;
by count;
run;

data subset;
if first.count then price = 0.73;
else price = 0.63;
run;

data subset1;
set yourdata;
where count <= 25000;
run;

proc sort data = subset1;
by count;
run;

proc append base = subset1 data = subset;
run;