CODEWITHSUNDEEP
sqloracle-databasesas
Papa Yatma Diop
Papa Yatma Diop

Reputation: 21

Use a macro in SAS 'to_date' conversion

Being forced to pass my code in explicit pass though, the date conversion no longer works. I would like to use the &aaaa macro in the convert function.

My code example :

%let aaaa = 2014;
proc sql;
EXECUTE(
create table new_table as (
select * from old_table
where date between to_date('01/01/&aaaa', 'dd/mm/yyyy') and to_date('01/01/&aaaa', 'dd/mm/yyyy')))
BY ORACLE;

I got an error :

"The (full) year must be between -4713 and +9999 and be different from 0".

Does anyone have a solution?

Upvotes: 0

Views: 1794

Answers (2)

Papa Yatma Diop
Papa Yatma Diop

Reputation: 21

I got an :

  • "ORACLE execute error: ORA-00904 : '2014-01-01' : not valid identificator " error by using to_date(&bbbb., 'dd/mm/yyyy') and
  • ORA-00936 : missing expression error by using DATE &aaaa.

Upvotes: 0

Reeza
Reeza

Reputation: 21274

Macro variables only resolve in double quotes not single quotes so you'll have to change that. IIRC Oracle has issues with to_date but you can also provide the dates as : DATE '2020-01-01' but I'd consider making the whole variable, including the quotes your macro variable just to make it a bit easier.

%let aaaa = '2014-01-01';

proc sql;
EXECUTE(
create table new_table as (
select * from old_table
where date between DATE &aaaa. and DATE &aaaa.))
BY ORACLE;

You can modify your original macro variable, aaaa to be in the format below if required. Untested:

%let bbbb = %sysfunc(quote(&aaaa-01-01, "'"));
%put &bbbb.;

Upvotes: 1

Related Questions