topic Re: First and Last day using proc - date is character in SAS Programming

First and Last day using proc - date is character

Q1983 — Sun, 28 Mar 2021 03:12:12 GMT

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

Re: First and Last day using proc - date is character

japelin — Sun, 28 Mar 2021 06:13:54 GMT

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;

Re: First and Last day using proc - date is character

Kurt_Bremser — Sun, 28 Mar 2021 07:06:28 GMT

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')

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

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