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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.