Using a SAS Macro Variable in Teradata SQL Where Clause

Reply
Occasional Contributor
Posts: 5

Using a SAS Macro Variable in Teradata SQL Where Clause

I have two variables, each stored in a SAS Macro Variable with the yymmdd10. format. When I hard code the dates using a %let statement (%let Start_Date = '2015-06-09'Smiley Wink the SQL runs fine but when I try to use macro variables in Teradata it gives an error, "Teradata execute: A character string failed conversion to a numeric value." I know the database field "some_date" is a date format and as stated above the hard coded Start_date format works fine. Any suggestions?

data _null_;

call symput('Start_Date', From_Date);

call symput('End_Date', To_Date);

run;

%let q=%BQUOTE(');

PROC SQL;

CONNECT TO TERADATA (connection information);

EXEC(COMMIT) BY TERADATA;

EXEC

(

INSERT INTO db_name.test_table

SELECT ...some_list...

FROM db_name.some_table

WHERE some_date between &q&Start_Date&q and &q&End_Date&q

) BY TERADATA;

EXEC(COMMIT) BY TERADATA;

DISCONNECT FROM TERADATA;

QUIT;

Grand Advisor
Posts: 9,576

Re: Using a SAS Macro Variable in Teradata SQL Where Clause

Not Sure if it could work. Code not test.

data _null_;

call symput('Start_Date', cats("'", put(From_Date,yymmdd10.),"'")   );

call symput('End_Date',  cats("'", put(To_Date,yymmdd10.),"'")  );

run;

PROC SQL;

CONNECT TO TERADATA (connection information);

EXEC(COMMIT) BY TERADATA;

EXEC

(

INSERT INTO db_name.test_table

SELECT ...some_list...

FROM db_name.some_table

WHERE some_date between &Start_Date and &End_Date

) BY TERADATA;

EXEC(COMMIT) BY TERADATA;

DISCONNECT FROM TERADATA;

QUIT;

Occasional Contributor
Posts: 5

Re: Using a SAS Macro Variable in Teradata SQL Where Clause

Xia,

The code did run but did not return any results. I was already formatting as a date using the following code:

create table work.ToDate as

select datepart(To_Date) as TO_DATE FORMAT=yymmdd10....

I think there is something else causing the issue between SAS and Teradata. Any other suggestions would be appreciated.

Super User
Super User
Posts: 6,332

Re: Using a SAS Macro Variable in Teradata SQL Where Clause

Since you did not specify a format for your SAS date variable in the DATA _NULL_ code you posted are you sure that SAS is generating the dates in the YYYY-MM-DD format that Teradata expects?  Also you might add the keyword DATE so that Teradata knows that you mean the quoted string as a date literal.

WHERE datevar BETWEEN DATE '2014-01-01' and DATE '2014-12-31'

You can add the single quotes to the macro variable values.  Or use a utility macro like %TSLIT() to add the quotes.  Or write your own macro.

See this discussion:

Occasional Contributor
Posts: 5

Re: Using a SAS Macro Variable in Teradata SQL Where Clause

Tom,

I output the dates into two tables to check whether the dates are in the correct format before passing to the Teradata code. The dates in the two tables look as follows:

From_Date: 2015-06-14

To_Date: 2015-06-20

I did try adding the keyword DATE and got a few errors. Remember the issue does not exist when I do not use a macro variable. When I hard code the string everything works fine.

As well, I used %let q=%BQUOTE('); to add the single quotes to the variable names. Is this the same as what you are suggesting.

Thanks for your help,

Adam

Super User
Super User
Posts: 6,332

Re: Using a SAS Macro Variable in Teradata SQL Where Clause

How the dates look when printed from the data set is not the question. The question is what the macro variables that you want to use to query TD look like.

As with any code generation project it is best to design what code you want to generate first. 

So if your dates are Jun14 to Jun20 th of this year then try seeing how many records that would find.

select * from connection to teradata

(select count(*) as nobs

FROM db_name.some_table

WHERE some_date between '2015-06-14' and '2015-06-20'

) ;

If that doesn't work then your issue is on the Teradata side.  Is "SOME_DATE" really a date variable?  Is your default date format different so the '2015-06-14' is not recognized as date literal?

If that works then work on the part that is creating the macro variables to make sure that the value looks like the code above.

data _null_;

   call symputx('STARTD',cats("'",put(from_date,yymmdd10.),"'");

run;

%put STARTD = &STARTD;

If you use macro quoting such as %BQUOTE to add the single quotes then you might also need to include a call to %UNQUOTE() macro function to remove the macro quoting so that SAS can properly parse the generated code.

Valued Guide
Posts: 3,206

Re: Using a SAS Macro Variable in Teradata SQL Where Clause

The Teradata dates are not stored in the way sas dates are stored.

Sas is not supporting sql-2013 and by that missing all interval functions of sql.

The result is is that constant date values may be translated correct onto teradata sql. But that is it, nothing more.

---->-- ja karman --<-----
Occasional Contributor
Posts: 5

Re: Using a SAS Macro Variable in Teradata SQL Where Clause

Japp,

Can you explain a little more what you are saying? I am not sure I understand.

Thanks,

Adam

Valued Guide
Posts: 3,206

Re: Using a SAS Macro Variable in Teradata SQL Where Clause

What  I am trying to explain is that within TD dates/times are stored as binary numbers types. That type is not known in SAS. Within TD the dates/times are stored by their decimal representation an not as a continous values as SAS does.

The implicit SQL translation can do a lot of the needed translations but cannot do all of them by some of those misalignment.  Working with dates times as you are used to with SAS functions will fail. With SQL2013 there are a lot of Datetime functions in the DBMS system. TD is supporting that. SAS is far behind with SQL-99.

The datepart function and assigning a format in sas is effectively killing the TD advantage. In those cases you are better with e plicit SQL as it will leave the work in TD. You can decide for views in TD avoiding additional data copies.

Xia posted an attempt to explicit pass through. The sql should be TD sql not SAS sql. There are many differences in those.

---->-- ja karman --<-----
Ask a Question
Discussion stats
  • 8 replies
  • 2632 views
  • 1 like
  • 4 in conversation