topic Re: How do I convert macro value of MMDDYYYY to work with a SAS Date? in SAS Programming

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

StickyRoll — Wed, 31 Aug 2022 04:04:05 GMT

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.

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

MarkusWeick — Wed, 31 Aug 2022 04:49:26 GMT

Hi @StickyRoll,

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

Best

Markus

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

mkeintz — Wed, 31 Aug 2022 12:54:22 GMT

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;

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

Kurt_Bremser — Wed, 31 Aug 2022 05:36:28 GMT

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;