"Extract From date" Breaking my Macro variable

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

"Extract From date" Breaking my Macro variable

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; 



 


Accepted Solutions
Solution
‎03-09-2018 09:42 AM
Super User
Super User
Posts: 8,078

Re: "Extract From date" Breaking my Macro variable

Posted in reply to lloydsanford

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))

View solution in original post


All Replies
Super User
Super User
Posts: 8,078

Re: "Extract From date" Breaking my Macro variable

Posted in reply to lloydsanford

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?  

 

 

Super User
Super User
Posts: 9,599

Re: "Extract From date" Breaking my Macro variable

Posted in reply to lloydsanford

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;
New Contributor
Posts: 3

Re: "Extract From date" Breaking my Macro variable

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?

Solution
‎03-09-2018 09:42 AM
Super User
Super User
Posts: 8,078

Re: "Extract From date" Breaking my Macro variable

Posted in reply to lloydsanford

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))
New Contributor
Posts: 3

Re: "Extract From date" Breaking my Macro variable

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 107 views
  • 0 likes
  • 3 in conversation