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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.