topic Re: Manipulating criteria for where statement in macro variables in SAS Programming

Manipulating criteria for where statement in macro variables

ColeG — Wed, 30 Aug 2017 17:22:36 GMT

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!

Re: Manipulating criteria for where statement in macro variables

Astounding — Wed, 30 Aug 2017 17:34:01 GMT

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.

Re: Manipulating criteria for where statement in macro variables

ColeG — Wed, 30 Aug 2017 17:47:10 GMT

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?

Re: Manipulating criteria for where statement in macro variables

ballardw — Wed, 30 Aug 2017 17:49:52 GMT

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.

Re: Manipulating criteria for where statement in macro variables

Astounding — Wed, 30 Aug 2017 17:56:19 GMT

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?

Re: Manipulating criteria for where statement in macro variables

ColeG — Wed, 30 Aug 2017 17:56:52 GMT

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

Re: Manipulating criteria for where statement in macro variables

ColeG — Wed, 30 Aug 2017 18:09:15 GMT

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.

Re: Manipulating criteria for where statement in macro variables

ballardw — Wed, 30 Aug 2017 20:03:57 GMT

@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.;