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.
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
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!
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,
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 |
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;
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.