Many SAS programmers struggle with date manipulation, particularly when creating dynamic, reusable code.
In a previous post, we covered converting a text string into a date variable, whether that string was hardcoded or stored in a variable. However, you could have a macro variable that looks like a date.
In this article, we assume the following before we convert a SAS macro variable to a date.
- You have a macro variable that looks like a date (e.g., 31DEC2020), and
- You want to create a new macro variable that contains the numeric representation of this date (e.g., 22280)
%let mydate = 2021-12-31; %put original value: &mydate; %let numeric_date= %sysfunc(inputn(&mydate, yymmdd10.)); %put numeric date : &numeric_date;
original value: 2021-12-31 71 %let numeric_date= %sysfunc(inputn(&mydate, yymmdd10.)); 72 %put numeric date : &numeric_date; numeric date : 22645
You use SYSFUNC and INPUTN to change a macro variable into a date.
First, you must use SAS functions outside a SAS Data Step or PROC SQL with the SYSFUNC function. Then, you use the INPUTN function to turn the macro variable that looks like a date into an actual macro date.
To display the formatted SAS date in the log, you can use the
%let date_d9=%sysfunc(inputn(&mydate, yymmdd10.), date9.); %put date9 format. : &date_d9; %let date_d9=%sysfunc(inputn(2021-12-31, yymmdd10.), date9.); %put date9 format. : &date_d9;
Note that the value passed in the input function should not be in quotes.
The INPUTN function is similar to the INPUT function because both convert text into numbers. However, you can use the INPUTN function in a %LET statement, whereas you can’t do this with the INPUT function.
The format of macro variables can be changed with the INPUTN, INPUTC or PUTN, PUTC functions.
To change a macro variable using numeric informat, use the INPUTN function.
To change a macro variable using a character format, use the PUTC function.
If you need to print it in a human-readable format in which case you can always use
val = %SYSFUNC(INPUTC(char val, informat)); val = %SYSFUNC(INPUTN(num val, informat)); val = %SYSFUNC(PUTC(char val, format)); val = %SYSFUNC(PUTC(num val, format))
Using macro dates as subsetting if OR WHERE statement
%let dt=01JAN2000; data dtates2; set sashelp.rent; where date gt &DT; run;
This throws an error because &dt is stored as a character.
The code works fine by adding a date literal in the macro variable.
%let dt='01JAN2000'D; data dtates2; set sashelp.rent; where date gt &DT; run;
Formatted SAS dates, which are text, must be enclosed in single or double quotes, followed by the letter D (since you are using a macro variable, it must be double quotes)
What if the macro variable contains a numeric representation of the date?
proc sql noprint; select min(date) into :mindatel from sashelp.buy; quit; %put &mindatel;
The example below results in an error because the value of &mindate is a number. Even if you apply quotes, it results in an error.
proc sql; select * from sashelp.rent where date > &mindate; quit; proc sql; select * from sashelp.rent where date > "&mindate"; quit;
ERROR: Expression using greater than (>) has components that are of different data types.
The solution is to add a date format to the macro variable while creating it and add the date literal while resolving the variable.
proc sql noprint; select min(date) format = date9. into :mindate from sashelp.buy; quit; proc sql; select * from sashelp.rent where date > "&mindate"D; quit; %put value is &mindate;
In conclusion, SAS macro variables offer a powerful and effective tool for manipulating dates in your code.
Whether you’re working with basic date calculations or more advanced techniques such as nested macro variables and array processing, SAS macro variables as dates provide a reliable and user-friendly solution.
We hope this post has given you a better understanding of the benefits and best practices for using SAS macro variables as dates and that you’ll be inspired to start incorporating them into your code.