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.
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;
Hi @StickyRoll,
have you tried the INPUT function (Microsoft Word - chap1new.doc (sas.com))?
Best
Markus
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;
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.