topic Re: insert date variable into table from a marcro in SAS Enterprise Guide

insert date variable into table from a marcro

DavidPhillips2 — Wed, 24 Oct 2018 13:47:17 GMT

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;

Re: insert date variable into table from a marcro

Reeza — Wed, 24 Oct 2018 13:57:52 GMT

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

Re: insert date variable into table from a marcro

DavidPhillips2 — Wed, 24 Oct 2018 14:03:26 GMT

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.

Re: insert date variable into table from a marcro

Astounding — Wed, 24 Oct 2018 14:04:12 GMT

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

Re: insert date variable into table from a marcro

Reeza — Wed, 24 Oct 2018 14:08:16 GMT

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.

Re: insert date variable into table from a marcro

RW9 — Wed, 24 Oct 2018 14:11:35 GMT

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&docsetVersion=3.1&locale=en

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

Re: insert date variable into table from a marcro

SuryaKiran — Wed, 24 Oct 2018 14:21:23 GMT

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;

Re: insert date variable into table from a marcro

DavidPhillips2 — Wed, 24 Oct 2018 14:48:06 GMT

Suryakrian,

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