Manipulating criteria for where statement in macro variables

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

Manipulating criteria for where statement in macro variables

I am working on creating tables using proc sql. I need to update these tables monthly and would like to only change one %let statement at the beginning in order to accomplish this, however I am currently restricted by the data formats in the data warehouse.

 

The year is stored as (for example) year_of_calendar = 2,017 and month is stored as month_of_year = 8. When referencing these two dates in the code, they must be contained in single quotes.

 

Below is my code so far:

%let yrmth=201708; /* update this every month */


/* never update these */
%let year=%unquote(%str(%'catx(',',substr(left(&yrmth),1,1),substr(left(&yrmth),2,3))%'));
/* this is supposed to turn 201708 into '2,017' as year */ %let month=%unquote(%str(%'mod(&yrmth,100)%'));
/* this is supposed to turn 201708 into '8' as month */ /* sql code that selects from only the month and year of interest */ proc sql; select * from datawarehouse where year_of_calendar=&year. and month_of_year=&month; quit;

 When I run this I get the following error:

 

ERROR: Teradata prepare: Syntax error, expected something like an 'ON' keyword between the word 'date_dim' and the 'date' keyword.

SQL statement was: select blah blah blah where

date.year_of_calendar='catx(',',substr(left(201708),1,1),substr(left(201708),2,3))' and date.month_of_year='mod(201708,100)'.

 

The &yrmth variable is reading in correctly but I can't the formulas themselves to work (I have verified that catx and mod statements do what I am expecting them to do when in a table, it is the %let statement that is giving me trouble).

 

Thanks!


Accepted Solutions
Solution
‎08-30-2017 02:10 PM
Super User
Posts: 6,644

Re: Manipulating criteria for where statement in macro variables

I see what you mean about the error.  I can't explain it, but it's not too difficult to work around:

 

%let yrmth = 201708;

 

%let year = %substr(&yrmth,1,1),%substr(&yrmth,2,3);

%let year = %unquote(%str(%'&year%'));

 

Of course it's easier if you're allowed to use doublequotes instead of singlequotes.  Would Teradata accept that?

View solution in original post


All Replies
Super User
Posts: 6,644

Re: Manipulating criteria for where statement in macro variables

Here is a list of functions that you are using, that macro language does not understand:  MOD, SUBSTR, CATX, LEFT

 

You could conceivably apply %SYSFUNC to each one of these, but that would be incredibly messy.  Instead, consider:

 

%let year = %unquote(%str(%'%substr(&yrmth,1,1),%substr(&yrmth,2,3)%'));

 

When you string text together, macro language just uses it.  You don't need CATX.

 

Then:

 

%let month = %unquote(%str(%'%eval(&yrmth - &yrmth/100*100)%'));

 

%EVAL performs integer arithmetic, and macro language follows the usual order of operations so the subtraction is performed last.

Occasional Contributor
Posts: 13

Re: Manipulating criteria for where statement in macro variables

Posted in reply to Astounding

Thank you for your help! However, when I try to run those statements I get errors.

 

For the year statement, it says there are too few arguments for the substr statements (I don't know why).

 

For the month statement, it says a character operand was found. Again, I don't know why, but is it because of the quotation marks?

Solution
‎08-30-2017 02:10 PM
Super User
Posts: 6,644

Re: Manipulating criteria for where statement in macro variables

I see what you mean about the error.  I can't explain it, but it's not too difficult to work around:

 

%let yrmth = 201708;

 

%let year = %substr(&yrmth,1,1),%substr(&yrmth,2,3);

%let year = %unquote(%str(%'&year%'));

 

Of course it's easier if you're allowed to use doublequotes instead of singlequotes.  Would Teradata accept that?

Occasional Contributor
Posts: 13

Re: Manipulating criteria for where statement in macro variables

Posted in reply to Astounding

Awesome! Yes that worked, thank you so much! Double-quotes don't seem to work, unfortunately.

 

I did the same thing with the month statement and the character operand error went away. So I think it probably was the quotes thing that led to the errors.

Super User
Posts: 13,358

Re: Manipulating criteria for where statement in macro variables

It would help a lot if you showed a version of this code:

proc sql;
select * from datawarehouse
 where year_of_calendar=&year. and month_of_year=&month;
quit;

That works with none of the macro stuff at all.

 

I will say that if I were dealing with only two parameters I do not see saving much time in updating

 

%let year = 2017;

%let month = 8;

instead of parsing out the bits from the string 201708.

Occasional Contributor
Posts: 13

Re: Manipulating criteria for where statement in macro variables

Without macros it would like

 

proc sql;
    select * from datawarehouse
    where year_of_calendar='2,017' and month_of_year='8';
quit;

 

And yes, you're right it would be essentially as fast to change two dates instead of one but at this point I just kind of want to figure out how this works. Also, I use the yrmth variable in multiple locations and it's usually a date range so it would be updating six+ variables each month. Again, yeah, that's not a big deal. But it would be kind of cool to be able to do this with just one or two variables

Super User
Posts: 13,358

Re: Manipulating criteria for where statement in macro variables


ColeG wrote:

And yes, you're right it would be essentially as fast to change two dates instead of one but at this point I just kind of want to figure out how this works. Also, I use the yrmth variable in multiple locations and it's usually a date range so it would be updating six+ variables each month. Again, yeah, that's not a big deal. But it would be kind of cool to be able to do this with just one or two variables


Multiple uses of the variable makes the parsing a little more valid. I will admit that I dislike the longish ugly macro code to such things in one call.

Here's my take on placing the quotes into the macro variable. I think the comma in the year is a tad odd but then I speak no Teradata.

%let  yrmth=201708; 

data _null_;
   y= input(substr("&yrmth",1,4),f4.);
   m= input(substr("&yrmth",5,2),f2.);
   ystr=cats("'",put(y,comma5.),"'");
   mstr=cats("'",m,"'");
   call symputx('year',ystr);
   call symputx('month',mstr);
run;

%put year=&year. month=&month.;
☑ This topic is solved.

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

Discussion stats
  • 7 replies
  • 263 views
  • 0 likes
  • 3 in conversation