insert date variable into table from a marcro

[DavidPhillips2]

 What is the syntax to insert a date macro variable into a table?  

proc sql;
select max(rundate) into: testvar SEPARATED BY '' from testtable;
create table rundatelist (
tablename varchar(100),
rundate date);

insert into rundatelist (tablename, rundate) values ('table1', to_date(&testvar));  /*this line does not work*/
select * from rundatelist;
quit;

[Reeza]

I think just remove the to_date() around the &testvar?

[DavidPhillips2]

Reeza if I remove to_date(), the log shows.

24OCT2018:04:34:39
_______
22
76
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant, a datetime constant,
a missing value, ), +, ',', -, MISSING, NULL, USER.

ERROR 76-322: Syntax error, statement will be ignored.

[Reeza]

Then wrap it in quotes and put a dt at the end.

"&testvar"dt -> this depends on what field type you're inserting it into. For a date time use the approach above. If it's character, then drop the dt after the quotes.

[Astounding]

Do you know what the correct syntax would be if you were hard-coding a value instead of referring to a macro variable?

[RW9]

To_date() is an oracle function yes?  Maybe:

to_date("&testvar.")

You may need to specify a format also.

Or do you mean this function:

https://documentation.sas.com/?docsetId=ds2ref&docsetTarget=n0l7ar188h314dn1dlgztkiknays.htm&docsetV...

Because that is expecting a number of days, so you would need to input() the text from that macro variable.

[SuryaKiran · Accepted solution]

Is your rundate values are in datetime format, then you might need to change your create table

data testtable;
format rundate datetime26.;
rundate='17OCT2018:14:45:32'DT;output;
rundate='18OCT2018:14:45:32'DT;output;
run;

proc sql;
select max(rundate) into: testvar SEPARATED BY '' from testtable;
create table rundatelist (
tablename varchar(100),
rundate num format=datetime26.);

insert into rundatelist (tablename, rundate) values ('table1', &testvar);  /*this line does not work*/
select * from rundatelist;
quit;

If you want to insert only date instead of datetime then use datepart() to extract date.

proc sql;
select datepart(max(rundate)) into: testvar SEPARATED BY '' from testtable;
create table rundatelist (
tablename varchar(100),
rundate date);

insert into rundatelist (tablename, rundate) values ('table1', &testvar);  /*this line does not work*/
select * from rundatelist;
quit;

[DavidPhillips2]

Suryakrian,

Thanks for your answer.  I included dt after to use the logic with a macro.