How do I convert macro value of MMDDYYYY to work with a SAS Date?

[StickyRoll]

How do I convert macro value of MMDDYYYY to work with a SAS Date?

I have a macro variable that stores value in "MMDDYYYY" format. Eg. 07311999.

I want to find a way to convert this 07311999 macro variable to a SAS Date so that it can work in a normal proc sql or data step.

This is my use case.

%let mmddyyyy_var = 07311999;

proc sql;

create table sasuk as

select *

from work.anything

where creation_date = &mmddyyyy_var.;

quit;

** creation_date is a normal sas date column.

As we can already tell from the sampel program above, this will not work as &mmddyyyy_var. is in 07311999 format. How can I convert this into a SAS Date for this sql statement to work?

I tried referring to :https://go.documentation.sas.com/doc/en/vdmmlcdc/8.1/ds2ref/n03d6ri46spzeln10bt1iepigfdy.htm

However, those conversion doesn't work for me in this case.

[MarkusWeick]

Hi @StickyRoll,

have you tried the INPUT function (Microsoft Word - chap1new.doc (sas.com))?

Best

Markus

[mkeintz · Accepted solution]

Instead of using a macro value in the mmddyyyy format, I'd suggest using the DATE9 format.  It's just as readable as your format, but is much easier to parse as a date literal value, as in:

%let cutdate=31jul1999;
proc sql;
  create table sasuk as
  select *
  from work.anything
  where creation_date = "&cutdate"d ;
quit;

However, if you must use the mmddyyyy format, then:

%let cutdate=07311999;
proc sql;
  create table sasuk as
  select *
  from work.anything
  where creation_date = %sysfunc(inputn(&cutdate,mmddyy8.));
quit;

[Kurt_Bremser]

Maxim 28: Macro Variables Need No Format.

%let mmddyyyy_var = %sysfunc(inputn(07311999,mmddyy8.));

proc sql;
create table sasuk as
select *
from work.anything
where creation_date = &mmddyyyy_var.;
quit;