BookmarkSubscribeRSS Feed
madamimadam
Calcite | Level 5

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;

8 REPLIES 8
Ksharp
Super User

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;

madamimadam
Calcite | Level 5

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.

Tom
Super User Tom
Super User

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:

madamimadam
Calcite | Level 5

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

Tom
Super User Tom
Super User

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.

jakarman
Barite | Level 11

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 --<-----
madamimadam
Calcite | Level 5

Japp,

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

Thanks,

Adam

jakarman
Barite | Level 11

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 --<-----

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
  • 8 replies
  • 7398 views
  • 2 likes
  • 4 in conversation