where statement not giving the exact results.

[Sandeep77]

Hi Experts,

I am trying to filter on a date column but not getting the result as expected. It gives the same result if I run without that filter. I want tx_date prior to the payment date. Here is the code:

proc sql;
create table Collection as
select distinct

a.*,
b.paymentdate,
b.tx_date,
b.tran_code,
sum(b.tx_amount) as Collected
from Payment_plans_full as a
inner join ABC.debt_trans as b on a.accountnumber=b.debt_code
where b.tx_date between '23SEP2021'd and '25NOV2021'd and
b.tx_date >= b.paymentdate and
(b.tran_code like 'DR%'
and b.tran_code not in ('DR3109', 'DR3108'))

group by a.accountnumber ;
quit;

Can you please suggest?

[PaigeMiller]

Hello, @Sandeep77 

You're not getting the results you want. The only way we can help is if you provide (a portion of) your data in data set ABC.debt_trans and Payment_plans_full in usable form. Here, "usable form" means SAS data step code, which you can type in yourself, or provide via these instructions. Other forms of providing the data are not acceptable.

In the future, please provide the data in this forum with your original post. That helps us give you the assistance you need. Don't make us ask to see the data.

[Patrick]

Investigate one row in our B source table where you would expect the filter to behave differently. 

One potential reason why things don't work as you'd expect for b.tx_date >= b.paymentdate could be that one of the date columns contains a SAS Date value and the other column a SAS DateTime value.

If you can't share the data then at least share a Proc Contents of your source tables.

[AMSAS]

Another approach to troubleshooting

Build you Where clause up, clause by clause checking each time you are getting the results you expect
e.g.
Run # 1 "where b.tx_date between '23SEP2021'd and '25NOV2021'd"
Run # 2 "where b.tx_date between '23SEP2021'd and '25NOV2021'd and b.tx_date >= b.paymentdate"

...

[ballardw]

---

@Sandeep77 wrote:

Hi Experts,

I am trying to filter on a date column but not getting the result as expected. It gives the same result if I run without that filter.

---

That means the filter is always true. So examine your values closely in the source data set.

[PGStats]

Maybe just a typo? You say you want "tx_date prior to the payment date", this means that tx_date must be inferior to paymentdate...

Now, if paymentdate is not between '23SEP2021'd and '25NOV2021'd then the comparison with tx_date will either be always true or always false.