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

Using a SAS Macro Variable in Teradata SQL Where Clause

madamimadam — Fri, 12 Jun 2015 17:46:09 GMT

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

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

Ksharp — Sat, 13 Jun 2015 06:20:28 GMT

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;

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

Tom — Sat, 13 Jun 2015 08:03:21 GMT

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:

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

jakarman — Sat, 13 Jun 2015 19:30:52 GMT

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.

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

madamimadam — Mon, 15 Jun 2015 14:55:29 GMT

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.

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

madamimadam — Mon, 15 Jun 2015 15:01:51 GMT

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

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

madamimadam — Mon, 15 Jun 2015 15:03:26 GMT

Japp,

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

Thanks,

Adam

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

Tom — Mon, 15 Jun 2015 16:03:20 GMT

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.

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

jakarman — Wed, 17 Jun 2015 05:56:18 GMT

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.