mainframe SAS: code is not working please. extract(year from(add_months(&tera_dt1, -&lapse) ))

[ManoharNath]

/** Here I am trying to pull previous 5 month data from mainframe in SAS EG.
but getting below error, don't know what need to be done now. please help. 
/*Generate same data for previous 5 months*/

rsubmit;
%macro mons(brand, brand2, n1,lapse);

proc sql;
create table &brand2._OD&n1. as
select 
branch_no as branch           
, account_no as acct   
, "&brand2." as brand
, account_open_date as opened 
, min_bal_cld as minbal     
, ACCOUNT_LIMIT as odlim    
, extract_date

from &brand..MIS_ACC
where EXTRACT (YEAR FROM extract_date) = extract(year from(add_months(&rep_dt, - &lapse) ))    
and extract( month from extract_date) = extract(month from(add_months(&rep_dt, - &lapse) ))    
and  account_type in (&mtas.) 
and  ACCOUNT_LIMIT > 0 
and  Min_Bal_Cld < 0
);
quit;

/** append brands level information in one table*/
data OVD;
set &brand2._OD&n1.;
run;

proc download data=OVD out=OVD;
run;
%mend mons;
%mons(A, A , 2, 1);
%mons(A, A, 3,  2);
%mons(B, B, 2,  1);
%mons(B, B, 3, 2);
endrsubmit;

below is  error log.

 MLOGIC(MONS):  Beginning execution.
MLOGIC(MONS):  Parameter BRAND has value A
MLOGIC(MONS):  Parameter BRAND2 has value A
MLOGIC(MONS):  Parameter N1 has value 2
MLOGIC(MONS):  Parameter LAPSE has value 1
MPRINT(MONS):   proc sql;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
SYMBOLGEN:  Macro variable BRAND2 resolves to A
SYMBOLGEN:  Macro variable N1 resolves to 2
SYMBOLGEN:  Macro variable BRAND2 resolves to A
SYMBOLGEN:  Macro variable BRAND resolves to A
NOTE: Line generated by the invoked macro "MONS".
299   (YEAR FROM extract_date) = extract(year from(add_months(&rep_dt, - &lapse) )) and extract( month from extract_date) =
            ----
            22
            202
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, ), *, **, +, ',', -, '.', /, <, <=, <>, =, >, >=, ?, AND, 
              BETWEEN, CONTAINS, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ¬, ¬=, |, ||, 
2                                                          The SAS System                               19:50 Monday, August 2, 2021

              ¯, ¯=.  

ERROR 202-322: The option or parameter is not recognized and will be ignored.

299!  (YEAR FROM extract_date) = extract(year from(add_months(&rep_dt, - &lapse) )) and extract( month from extract_date) =
                                              ----
                                              22
299! extract(month from(add_months(&rep_dt, - &lapse) ))     and  account_type in (&mtas.) and  ACCOUNT_LIMIT > 0 and  Min_Bal_Cld <
299!  0 );
SYMBOLGEN:  Macro variable REP_DT resolves to 01Jun2021
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN, 
              CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ¬=, |, ||, ¯=.  

NOTE: Line generated by the macro variable "REP_DT".
299  01Jun2021
       -------
       22
SYMBOLGEN:  Macro variable LAPSE resolves to 1
NOTE 137-205: Line generated by the invoked macro "MONS".

[ballardw]

1) No attached error

2) When asking about errors we should have the log that generated the errors, not just the error.

3) When the error comes out of macro coding you need to set: Options Mprint;

before running the macro to generate more details in the log about the code the macro creates. Otherwise an error after a macro runs doesn't show where in the code it happened. Mprint will have the error in relation to the code, or at least better proximity.

4) copy the log with all the mprint messages, the code and error and paste the text into a text box opened on the forum with the </> icon to preserve formatting.

[Amir]

Hi @ManoharNath,

As @ballardw has pointed out, the log will help in the diagnosis of the issue.

I did notice your use of the extract() function to extract a year and a month. As extract() is not a SAS function, you might want to consider using SAS functions year()  and month().

Thanks & kind regards,

Amir.

[Kurt_Bremser]

There is neither a EXTRACT nor a ADD_MONTHS function in SAS. You should look into the INTNX function for calculating intervals, and the YEAR function for getting the year from a date.

[jimbarbour]

@ManoharNath,

This:

where EXTRACT (YEAR FROM extract_date) = extract(year from(add_months(&rep_dt, - &lapse) ))    
and extract( month from extract_date) = extract(month from(add_months(&rep_dt, - &lapse) ))

isn't really SAS code.  It looks more like Hive or Oracle or something.  If you use Proc SQL with a Libname, you need to use SAS syntax for your SQL.

If you want to use the syntax of the data base, you need to issue a CONNECT first and then code what is referred to as "explicit pass through SQL."  If you Google "SAS explicit pass through", you should find some examples, if that's what you're trying to do.

Jim

[ManoharNath · Accepted solution]

Hi All, I've written my code in other way and able to solve this issue. just for reference we don't have to use macro parameters all the time, as in my questions I am pulling past 5 month data, for this we just had to apply filter on required column.

example: for June month, Start date would be 1st Jan 2021 and end date would be 31st May 2021.

Just apply this date on extract column like below

where extract date like : extract >= "01Jan2021"d and extract <= 31May2021"d 
and  account_type in (&mtas.) 
and  ACCOUNT_LIMIT > 0 
and  Min_Bal_Cld < 0