BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Tom
Super User Tom
Super User

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
Opal | Level 21

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.

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 16 replies
  • 2850 views
  • 2 likes
  • 6 in conversation