BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DavidPhillips2
Rhodochrosite | Level 12

 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;
1 ACCEPTED SOLUTION

Accepted Solutions
SuryaKiran
Meteorite | Level 14

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;

 

Thanks,
Suryakiran

View solution in original post

7 REPLIES 7
Reeza
Super User
I think just remove the to_date() around the &testvar?
DavidPhillips2
Rhodochrosite | Level 12

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
Super User
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
PROC Star

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Meteorite | Level 14

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;

 

Thanks,
Suryakiran
DavidPhillips2
Rhodochrosite | Level 12

Suryakrian,

 

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

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

Creating Custom Steps in SAS Studio

Check out this tutorial series to learn how to build your own steps in SAS Studio.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 4472 views
  • 4 likes
  • 5 in conversation