SAS date filters

[SASAna]

Hi SAS users,

I need help with date filters. I created test1 and using those paid dates for test2 dataset where conditions.

proc sql;
create table test1 as
select service_date ,
datepart(service_date) FORMAT YYMMDDD10. as fdos,
datepart(paid_date) FORMAT YYMMDDD10. as paid
from
test;
Quit;

proc sql;
create table test2 as
select
*
from test1
where paid between '2019-05-01' and '2020-04-30'    ( need help here how to get paid into character format )
;
Quit;

Thanks,

Ana

[PaigeMiller]

---

@SASAna wrote:

Hi SAS users,

I need help with date filters. I created test1 and using those paid dates for test2 dataset where conditions.

proc sql;
create table test1 as
select service_date ,
datepart(service_date) FORMAT YYMMDDD10. as fdos,
datepart(paid_date) FORMAT YYMMDDD10. as paid
from
test;
Quit;

proc sql;
create table test2 as
select
*
from test1
where paid between '2019-05-01' and '2020-04-30'    ( need help here how to get paid into character format )
;
Quit;

---

I believe you simply need to change the WHERE clause

where paid between '01MAY2019'd and '30APR2020'd

Formats applied to the variable PAID are irrelevant here — if you have a numeric date variable (which PAID is), they can be formatted with any date format you want, it is completely irrelevant to the WHERE clause because when SAS does the comparisons, it uses the unformatted date number. To determine if it is between two dates that you want to hard code, then you must use the formatting of '01MAY2019'd and no other formatting (except that the letters can be either upper or lower case, and you can usually get away with using 2 digit years).

You can combine your two PROC SQL blocks into a single call to PROC SQL

proc sql;
create table test1 as
select service_date ,
datepart(service_date) FORMAT YYMMDDD10. as fdos,
datepart(paid_date) FORMAT YYMMDDD10. as paid
from test 
where datepart(paid_date) between '01MAY2019'd and '30APR2020'd;
quit;