topic Re: Macro variable with date9. format in SAS Programming

Macro variable with date9. format

David_Billa — Sat, 25 Mar 2023 19:17:03 GMT

Following program yields the value of macro variable like 25MAR2023 which is similar to date9. format but without the single quotes and 'd'. But I need the value like '25MAR2023'd. Shed somelight on this issue to fix it.

data _null_;
start_date_edh = put(intnx("month",today(),-3,"b"), date9.);
end_date_edh = put(today()-1, date9.);
call symput('start_date_edh', start_date_edh);
call symput('end_date_edh', end_date_edh);
run;

Re: Macro variable with date9. format

PaigeMiller — Sat, 25 Mar 2023 20:37:28 GMT

@David_Billa wrote:

Following program yields the value of macro variable like 25MAR2023 which is similar to date9. format but without the single quotes and 'd'. But I need the value like '25MAR2023'd. Shed somelight on this issue to fix it.
data _null_;
start_date_edh = put(intnx("month",today(),-3,"b"), date9.);
end_date_edh = put(today()-1, date9.);
call symput('start_date_edh', start_date_edh);
call symput('end_date_edh', end_date_edh);
run;   

In most cases, you DO NOT need values with quotes around it. In most cases, you DO NOT want to format macro variables. See Maxim 28.

data _null_;
start_date_edh = put(intnx("month",today(),-3,"b"));
end_date_edh = put(today()-1);
call symput('start_date_edh', start_date_edh);
call symput('end_date_edh', end_date_edh);
run;

gives you macro variables that you can use for any logical or arithmetic purpose. In most cases, an unformatted macro variable will work. If your variable is a true SAS date value (regardless of how it is formatted) this will work:

data want;
    set have;
    if date>&start_date_edh then do ... ;

Re: Macro variable with date9. format

Kurt_Bremser — Sat, 25 Mar 2023 20:58:52 GMT

@David_Billa wrote:

But I need the value like '25MAR2023'd.

No you don't. Use the unformatted, raw date value and you can use the macro variable without further ado anywhere the value is needed.

Formatted macro variables are only needed when you use them in a place where humans will read them (TITLEs, FOOTNOTEs and the like).

data _null_;
call symputx('start_date_edh',intnx("month",today(),-3,"b"));
call symputx('end_date_edh',today() - 1);
run;

Re: Macro variable with date9. format

Tom — Sat, 25 Mar 2023 21:03:20 GMT

You can just add them when you need them at the point where you USE the macro variable.

TITLE "Date between &start_date_edh and &end_date_edh";
proc print data=have;
  where date between "&start_date_edh"d and "&end_date_edh"d;
run;

But if you never need to use the macro variable to print text that humans will read you can just skip formatting the dates.

data _null_;
  call symputx('start_date_edh', intnx("month",today(),-3,"b"));
  call symputx('end_date_edh', today()-1);
run;   
proc print data=have;
  where date between &start_date_edh and &end_date_edh;
run;

PS Unless you need to include leading or trailing spaces in the macro variable values you should never use the ancient CALL SYMPUT() function. Use the more modern CALL SYMPUTX(), it has more flexibility.

Re: Macro variable with date9. format

s_lassen — Mon, 27 Mar 2023 09:32:45 GMT

In most cases, you DO NOT need values with quotes around it. In most cases, you DO NOT want to format macro variables. See Maxim 28.

I strongly disagree. If you are trying to find out what happens in a SAS program with code like

where date between 12341 and 12418;

you will probably waste quite a long time trying to find out, compared to

where date between "01JAN2017"d and "31DEC2017"d

which what your MPRINT output will look like with formatted values.

Already when you create the values, you will probably want to see the macro dates as text and not numbers. Just looking at values like 30DEC2022 makes it a lot easier to see if you actually did your date calculation correctly (yes, I think even YOU may sometimes make an error when writing formulas for calculating dates, and I know for certain that I do once in a while).

Re: Macro variable with date9. format

PaigeMiller — Mon, 27 Mar 2023 09:46:00 GMT

@s_lassen wrote:

In most cases, you DO NOT need values with quotes around it. In most cases, you DO NOT want to format macro variables. See Maxim 28.

I strongly disagree. If you are trying to find out what happens in a SAS program with code like
where date between 12341 and 12418;
you will probably waste quite a long time trying to find out

The goal isn't getting the log that works best, the goal is getting code that works best. By using un-formatted macro variables, you reduce the probability of errors, since you don't have the format the macro variable and you don't have to "un-format" the macro variable by adding quotes and a d on the end. Fewer opportunities for mistakes, fewer opportunities for typographical errors, less typing.

And why would it take a long time to figure out what 12341 represents? You know its a date value, it would take much less than a minute to figure this out.

Re: Macro variable with date9. format

Kurt_Bremser — Mon, 27 Mar 2023 17:30:42 GMT

On top of what Paige already said, only WHERE statements, clauses or dataset options will display such values on their own. If the macro variable is used in calculations, conditions or as argument to a function, there is no automatic display of the value, so a PUT must be used anyway for control, where a format is easily added.

In my professional SAS life, macro variables were either created through data steps, where a PUT is easily added, or imported from the scheduler via %SYSGET, and the INCLUDE which did this for all programs wrote all imported values to the log on its own.

Writing a quick data _null_ to display a value was never much of an issue, compared to the much easier use of unformatted variables, which usually happened multiple times within a program.

Re: Macro variable with date9. format

ErikLund_Jensen — Mon, 27 Mar 2023 17:54:10 GMT

Hi @David_Billa

In SAS, dates should never be kept in a data set as text values, but always converted to SAS Date values. Similar with macro variables, they should also contain SAS Date values unless they are created for the sole purpose of being human-readable, e.g. as part of a title statement. This way the macro variables can always be used in where-statements, data step functions etc. without any conversion, so the coding becomes simpler and less error-prone.

But it is also true that it is nice to have the date values written to the log in a readable form, as @s_lassen advocates. It is always a good practice to write a macro variable to the log when it has been assigned a value, and with a little effort, one can have both:

%let date_start = %sysfunc(intnx(month,%sysfunc(today()),-3,b));
%put &=date_start (%sysfunc(putn(&date_start,date9.)));

DATE_START=22980 (01DEC2022)

Re: Macro variable with date9. format

s_lassen — Tue, 28 Mar 2023 08:06:44 GMT

Or you could simply write

%let date_start = "%sysfunc(intnx(month,%sysfunc(today()),-3,b),date9.)"d;
%put &=date_start;

And you will get

DATE_START="01DEC2022"d

Which is a SAS date constant. I do not understand why you think it is so important to keep the MPRINT output and the actual macro variable values unreadable.

Re: Macro variable with date9. format

ErikLund_Jensen — Tue, 28 Mar 2023 09:33:58 GMT

Hi @s_lassen

You wrote I do not understand why you think it is so important to keep the MPRINT output and the actual macro variable values unreadable.

We normally run production jobs with NOMPRINT, and MPRINT is only activated for debugging. That's why I like to put macro variable values in a human-readable form, so they appear in the log anyway and can be used in a first-level-debugging without having to read thousands of MPRINT lines.

And I really see date constants in macro variables as a nuisance. A SAS Date value will always work, but a date constant in a macro variable might give all kinds of trouble. It cannot be used in a macro calculation, and the presence of quotes in the value can also be troubleome, e.g. when building strings for CALL Execute using CAT-functions.

Consider this example:

161    %let date_start = "%sysfunc(intnx(month,%sysfunc(today()),-3,b),date9.)"d;
162    %let date_start = %eval(&date_start + 7);
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric
       operand is required. The condition was: "01DEC2022"d + 7
163    %put &=date_start;
DATE_START=

164
165    %let date_start = %sysfunc(intnx(month,%sysfunc(today()),-3,b));
166    %let date_start = %eval(&date_start + 7);
167    %put %sysfunc(putn(&date_start,date9.));
08DEC2022

Best greetings

Erik

Re: Macro variable with date9. format

s_lassen — Tue, 28 Mar 2023 10:27:22 GMT

OK, I beg to differ. The calculation

%eval(&date_start + 7);

can easily be done using %sysfunc(intnx()):

%let date2="%sysfunc(intnx(day,&date1,7),date9.)"d;

and as for CALL EXECUTE, the answer is: I don't. When I need to have a data step generate code, I write the code to a temporary SAS file, which I then %INCLUDE. So that I can see the generated code before submitting, and I have all the wonderful flexibility and power of the PUT statement available when generating the code.

But actually, the CATx functions work with date constants as well as with numbers, e.g.


1    data _null_;
2      a=cats('date in(',12345,',',"01DEC2004"d,')');
3      put a=;
4    run;

a=date in(12345,16406)

If you need to do something funny with macro variables containing quotes in a data step, just SYMGET them.

I think readable dates and datetimes make programs a lot easier to debug and modify, and most of the time spent on a program is generally spent on maintenance, not on the initial coding.

Re: Macro variable with date9. format

Quentin — Tue, 28 Mar 2023 13:03:23 GMT

Just to join in, I'm also a fan of using date literals as values for macro variables, and a fan of always leaving MPRINT turned on. (Nothing worse than seeing a log with an error in it but not seeing the code).

It's unfortunate that %EVAL() doesn't know about date literals, but of course it has other limitations as well. Happily %SYSEVALF knows about date literals:

1    %let date_start = "%sysfunc(intnx(month,%sysfunc(today()),-3,b),date9.)"d;
2    %let date_start = %sysevalf(&date_start + 7);
3    %put %sysfunc(putn(&date_start,date9.));
08DEC2022

If I'm writing a macro that accepts a parameter that is a date, I think best practice is to accept either a SAS date or a date literal.

I agree with the general guidance that value of date macro variable should not be "formatted", so I wouldn't use:

%let mydate=03/28/2023;
%let mydate=28Mar2028;

But I see a date literal as different than a formatted date string. Because a date literal has a known numeric value.