I had a database like this:
Account_Number | Month | Payment_Method |
1 | 0 | |
1 | 1 | PI |
1 | 2 | PI |
2 | 0 | |
2 | 1 | |
2 | 2 | |
2 | 3 |
For some data record error reason, for example, as the account number = 1, when month =0, the payment method should be PI instead of missing. but the account number 2 is fine. Since all of its months should be missing. So the correct database would be :all of the months should be PI or all of the months should be missing for the same account.
So my question is: for the account like 1, is that possible to let payment method = PI when month = 0? The problem only occur when month = 0 in the problem account.
I try CASE WHEN MONTH = 1 AND Payment_method= 'PI'
THEN (CASE WHEN MONTH =0 THEN PAYMENT_METHOD = 'P' END)
end
group by Account_number
It doesn't work at all...
Thanks in advance
You can use MAX/MIN with SQL.
proc sql;
create table want as
select *, max(payment_method) as payment_method_max
from have;
quit;
Thank you! I added a group by and it works out!!!
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.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.