Comparing date values in where clause

[Tom]

So ACTIVITY_DATE does NOT have DATE values. It has DATETIME values.  So you want to compare to DATETIME values (number of seconds since 1960) and not a DATE values (number of days since 1960).

If you want to use a macro variable to hold DATETIME values then either leave it with the raw number of seconds since 1960 and don't add the quotes.

CALL SYMPUTX('MonthStart',INTNX('DTMONTH',DATETIME(),0,'B'));
...
WHERE ACTIVITY_DATE>=&MonthStart. ...

or generate it in a format that the DATETIME informat can read and add the quotes and the dt suffix.

CALL SYMPUTX('MonthStart',put(INTNX('DTMONTH',DATETIME(),0,'B')),datetime20.);
...
WHERE ACTIVITY_DATE>="&MonthStart."dt ...

A couple of other things. Don't use the old CALL SYMPUT() instead of the newer CALL SYMPUTX() unless you really need to store leading and/or trailing spaces into the macro variable.

Speaking of leading spaces why does one of your variables appear to have a leading space in the photograph of the output of proc contents?  Is that really a space? Is it an underscore that is just blending into the border lines? If it really is a leading space then you will need to use a name literal to reference that variable.  

' ACCOUNT_NUM'n

[Patrick]

Given that you're comfortable using Toad to write and test Oracle syntax it's at current stage may be easier for you to use the exactly same Oracle syntax also within a SAS program (=you can copy/paste the syntax from Toad to SAS).

Using SQL this way is called Explicit pass-through SQL in SAS documentation. Explicit SQL gets sent by SAS without any changes directly to the database for execution.

In below code the explicit pass-through SQL is within the brackets of the ...from connection to (<explicit Oracle SQL>).

SAS Macro code gets pre-processed and though you can still use SAS Macros in explicit pass-through SQL. The SAS macro variables get resolved before SAS sends the SQL to the database.

I couldn't test it but below should work based on all the code you've already shared with us. It assumes that libref DB points to the Oracle database.

data _null_;
  call symputx('monthpartition',put(intnx('month',today(),-1,'b'),yymmn6.));
  call symputx('monthstart',cats("'",put(intnx('month',today(),0,'b'),yymmdd10.),"'"));
  call symputx('monthstartPlus1',cats("'",put(intnx('month',today(),1,'b'),yymmdd10.),"'"));
  stop;
run;
%put &=monthpartition;
%put &=monthstart;
%put &=monthstartPlus1;

proc sql;
  connect using db as ora;
  create table current_month_activity as
  select * from connection to ora
  (
    SELECT BASE.*, K.COL1, K.COL2
    FROM 
      MI_MONTH_END_BASE_&MonthPartition BASE 
      LEFT JOIN 
      K_MASTER K
        ON BASE.ACCOUNT_NUM=K.ACCOUNT_NUM AND BASE.SUBSCRIPTION=K.SUBSCRIPTION
           and K.ACTIVITY_DATE >= date &monthstart and K.ACTIVITY_DATE < date &monthstartPlus1
  )
  ;
quit;

The word doc you've posted shows that the data types for the same named variables in the two SAS tables are different. Based on the code you've shared I don't really understand how this could happen and further investigation would be necessary (like: What are the data types in the Oracle tables). But let's park this for now and try making the explicit pass-through approach working.

Should this not work:

Copy the explicit-pass through bit into a text editor, replace the macro variables 1:1 to what they resolve to (=what the %put statements print into the SAS log as values) and then execute the code in Toad. Debug the code in Toad, copy/paste the fully working code back into SAS and replace the fixed strings with the macro variables.