topic Re: mainframe SAS: code is not working please. extract(year from(add_months(&tera_dt1, -&lap in SAS Programming

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

ManoharNath — Mon, 02 Aug 2021 19:23:56 GMT

/** 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".

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

ballardw — Mon, 02 Aug 2021 15:34:45 GMT

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.

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

Amir — Mon, 02 Aug 2021 16:30:02 GMT

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.

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

Kurt_Bremser — Mon, 02 Aug 2021 17:01:50 GMT

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.

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

jimbarbour — Mon, 02 Aug 2021 23:53:51 GMT

@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

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

ManoharNath — Fri, 06 Aug 2021 06:52:24 GMT

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