BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
aperansi
Quartz | Level 8

 

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

View solution in original post

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

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
Obsidian | Level 7 ewv
Obsidian | Level 7
Glad to help! Been working on my SAS certifications so this is good practice. We'll solve it one piece at a time 🙂
ballardw
Super User

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

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. 

 

DatevendormidproductCountPrice
2018-09MicroBiltOnlineBank Account Verify25,0000.73
2018-09MicroBiltOnlineBank Account Verify37,3090.63
pink_poodle
Barite | Level 11
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;

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

Mastering the WHERE Clause in PROC SQL

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.

Discussion stats
  • 11 replies
  • 1585 views
  • 7 likes
  • 4 in conversation