PROC SQL INSERT CURRENT_TIMESTAMP Teradata

[deleted_user]

So I’m confused. I am using pass through SQL because I specified the connection and not the lib name. My understanding is I need to format such that Teradata understand the SQL. Which I have done. However I seem to not have done so that SAS will process it. I can not use Teradata sub-commands in pass through ? If not how can I accomplish what I am trying to do below and insert the current timestamp. I can create a data set and build the date and put to a variable, but is the REALLY the best way to do this ?

I get a syntax error because it can not recognize current_timestamp. Any help would be appreciated.

%macro updatingtables;

proc sql;
connect to teradata (user=user password="password"
server=server1 mode=teradata);
execute(INSERT INTO TDDatabase.Tablename
( column1
,column2
,column3
,column4
,column5
,column6
,column7 )
VALUES
("&data1"
,"&data2"
,"&data3"
,"&data4"
,"&data5"
,'COMPLETE'
,CURRENT_TIMESTAMP) );
by teradata;
disconnect from teradata;
quit;

%mend updatingtables;

%updatingtables

[deleted_user]

I will answer this in the event someone else needs the same information.

a) I had a semicolon incorrectly oustide the parens at the end of my value list
b) I had an issue with needing double quotes to resolve the variable, and needing single quotes for Teradata. The %str lets me resolve the variable and still use single quotes.
c) casting the current_timestamp let me get the value i needed.

I'm relatively new to SAS, and forums like this are invaluable for developers. Hopefully this information is helpful.


%macro updatingtables;

proc sql;
connect to teradata (user=user password="password"
server=server1 mode=teradata);
execute(INSERT INTO TDDatabase.Tablename
( column1
,column2
,column3
,column4
,column5
,column6
,column7 )
VALUES
( %str(%')&data1%str(%')
,%str(%')&data2%str(%')
,%str(%')&data3%str(%')
,%str(%')&data4%str(%')
,%str(%')&data5%str(%')
,'COMPLETE'
,CAST((CURRENT_TIMESTAMP) AS TIMESTAMP(6)); )
by teradata;
disconnect from teradata;
quit;

%mend updatingtables;

%updatingtables

Message was edited by: Sprtsnut Message was edited by: Sprtsnut