BookmarkSubscribeRSS Feed
Sandeep77
Lapis Lazuli | Level 10

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?

5 REPLIES 5
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
Patrick
Opal | Level 21

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
SAS Super FREQ

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
Super User

@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
Opal | Level 21

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.

PG

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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
  • 5 replies
  • 833 views
  • 1 like
  • 6 in conversation