First and Last day using proc - date is character

[Q1983]

data have;
infile datalines;
input my_date $20.;
return;
datalines;
02/01/2021
02/28/2021
01/21/2021
;run;

proc sql;
create table want as
select input(my_date,mmddyy10.) as new_date format=mmddyy10.
from have
where input(my_date,mmddyy10.) between first_day(add_months(current_date, -1))
and last_day(add_months(current_date, -1))
;quit;

The initial data shows the date as a character.  I attempted to convert it to a date.  I then want to select a date range however since the date initially is a character, how would I convert it so I can eliminate the data match errors

[japelin · Accepted solution]

Just replace the functions that cannot be used in proc sql with SAS functions.

proc sql;
  create table want as
    select input(my_date,mmddyy10.) as new_date format=mmddyy10.
    from have
    where input(my_date,mmddyy10.) between INTNX('MONTH', today(), -1,'BEGINNING')
      and INTNX('MONTH', today(), -1,'END')
;
quit;

[Kurt_Bremser]

This needs to be fixed at the point where the data enters your SAS environment.

Date values must (I use that word intentionally here) always be stored as SAS dates.

From where does the data originate (database, text file, Excel spreadsheet, ...)?

Next, you need to use ANSI SQL and SAS syntax, the available columns, and the functions available in SAS. I guess you want this:

where intnx('month',today(),0,'b') le my_date le intnx('month',today(),0,'e')

or

where month(my_date) = month(today()) and year(my_date) = year(today())

or

where put(my_date,yymmn6.) = put(today(),yymmn6.)