topic Re: "Extract From date" Breaking my Macro variable in SAS Programming

"Extract From date" Breaking my Macro variable

lloydsanford — Fri, 09 Mar 2018 13:38:24 GMT

I have the below code that seems not to work the error seems to the "FROM" in my macro EXTRACT (Year FROM <DATE>) see the error below

75    EXTRACT(MONTH FROM ADD_MONTHS(CURRENT_DATE,0))
                    ----
                    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, ^, ^=, |, ||, ~, ~=.

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

This is the code

rsubmit;
%macro monmac (month=,tablename=, year=);
proc sql;

create table homedir.Table2 as 
(select 

subproduct,
travel_year,
travel_month,
brx_anclry_rev_vlu

FROM Homedir.Anc_Flown_All

WHERE travel_year = &year.
AND travel_month = &month.);
quit;
%mend monmac;
endrsubmit;

rsubmit;
%monmac(tablename = Forecast1, month = EXTRACT(MONTH FROM ADD_MONTHS(CURRENT_DATE,0)),year = EXTRACT(YEAR FROM ADD_MONTHS(CURRENT_DATE,0)))
run;
endrsubmit;

Re: "Extract From date" Breaking my Macro variable

Tom — Fri, 09 Mar 2018 14:21:45 GMT

Doesn't really have anything to do with macro variable or macro processing.

There is not EXTRACT() function that is supported by PROC SQL.

What are you trying to do?

Re: "Extract From date" Breaking my Macro variable

RW9 — Fri, 09 Mar 2018 14:26:41 GMT

And where do you get this extract() function and syntax from? Is it MySql or Oracle or something? SAS proc sql only supports ANSI SQL and SAS functions. What I think you want is something like:

%monmac (month=,tablename=,year=);

proc sql;
  create table homedir.Table2 as 
  select  subproduct,
          travel_year,
          travel_month,
          brx_anclry_rev_vlu
  from    homedir.anc_flown_all
  where   travel_year=&year.
    and   travel_month=&month.;
quit;

%mend monmac;

%monmac (tablename=forecast1,month=%str(month(intnx('months',today(),0))),year=%str(year(intnx('year',today(),0))));

Although I can't see why you would want to do all that in the first place as the macro part adds nothing to the code at all, you can re-write like this, which is far simpler:

%let inc=0;

proc sql;
  create table homedir.Table2 as 
  select  subproduct,
          travel_year,
          travel_month,
          brx_anclry_rev_vlu
  from    homedir.anc_flown_all
  where   travel_year=intnx('year',today(),&inc.)
    and   travel_month=intnx('month',today(),&inc.);
quit;

Re: "Extract From date" Breaking my Macro variable

Tom — Fri, 09 Mar 2018 14:33:22 GMT

What did you think this expression was going to do?

EXTRACT(MONTH FROM ADD_MONTHS(CURRENT_DATE,0))

Looks like you might have meant to code something like this instead:

MONTH(CURRENT_DATE)

Or possibly

MONTH(datepart(CURRENT_DATE))

Re: "Extract From date" Breaking my Macro variable

lloydsanford — Fri, 09 Mar 2018 14:37:04 GMT

Hi thanks for replying i got the Extract from terdata which is where i coded the SQL, so is the problem that I am using TERADATA functions in SAS?

Re: "Extract From date" Breaking my Macro variable

lloydsanford — Fri, 09 Mar 2018 14:42:54 GMT

Thanks tom i see the error I made I was using Teradata syntax in SAS thanks