Quartz | Level 8

## If statement to calculate price based on count

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Obsidian | Level 7

## Re: If statement to calculate price based on count

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
11 REPLIES 11
Obsidian | Level 7

## Re: If statement to calculate price based on count

``````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

Quartz | Level 8

## Re: If statement to calculate price based on count

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.
Obsidian | Level 7

## Re: If statement to calculate price based on count

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
Quartz | Level 8

## Re: If statement to calculate price based on count

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.
Obsidian | Level 7

## Re: If statement to calculate price based on count

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.
Quartz | Level 8

## Re: If statement to calculate price based on count

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?
Obsidian | Level 7

## Re: If statement to calculate price based on count

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

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

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!

Obsidian | Level 7

## Re: If statement to calculate price based on count

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

## Re: If statement to calculate price based on count

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,

Quartz | Level 8

## Re: If statement to calculate price based on count

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
Barite | Level 11

## Re: If statement to calculate price based on count

``````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;``````
Discussion stats
• 11 replies
• 1637 views
• 7 likes
• 4 in conversation