Filter dataset by for March 2024

[Justin9]

Hi,

I have a dataset called test_dataset and want to only keep accounts where the c_EFF_DT variable is in March 2024 (so in example below, only the account with 02MAR2024 would remain). My code below isn't filtering correctly, so can someone help me to correct it please?

data test_output;
   set test_dataset;
   where "01Mar2024"d <= c_EFF_DT <= "31Mar2024"d;
run;

[PaigeMiller · Accepted solution]

Your code would work if c_EFF_DT was an actual SAS date value. But it is not a SAS date, it is a SAS date/time value. How do I know? Because it has been assigned a date/time format.

So to do the filtering, you have to use date/time values

where "01Mar2024:00:00:00"dt <= c_EFF_DT <= "31Mar2024:00:00:00"dt;

or even simpler, convert c_eff_dt to a date value

 where "01Mar2024"d <= datepart(c_EFF_DT) <= "31Mar2024"d;

or if you are going to be doing this repeatedly for future months, and you don't want to have to figure out what the last day of each month is and then type that into the program

where month(datepart(c_eff_dt))=3 and year(datepart(c_eff_dt))=2024;

or

where '01MAR2024'd <= datepart(c_eff_dt) < '01APR2024'd

Note the change from <= to < 

I like the last one best, less typing, no need to mentally figure out what the last day of the month is