BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ColeG
Obsidian | Level 7

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

7 REPLIES 7
Astounding
PROC Star

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.

ColeG
Obsidian | Level 7

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?

Astounding
PROC Star

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?

ColeG
Obsidian | Level 7

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.

ballardw
Super User

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.

ColeG
Obsidian | Level 7

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

ballardw
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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