Can we use case when and group by together?

[Linmuxi]

  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

[Reeza]

You can use MAX/MIN with SQL.

proc sql;
create table want as
select *, max(payment_method) as payment_method_max
from have;
quit;

[Linmuxi]

Thank you! I added a group by and it works out!!!