Using SAS Macro Variable As Date

We have now discussed converting a text string (either hardcoded text or a variable) into a date 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 as 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 %SYSFUNC(PUTN(...))

Syntax:

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)

It is important to create a new variable for conversion. By using the same variables as before, variables will remain characters.

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.

Solution:

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;

Using SAS macro variable as date

Using macro dates as subsetting if OR WHERE statement

Related

Subhro