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?

Answer

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.;