Help with converting date in mmddyy10. format to date9 to be used in macro calculations

[kissybean]

Hi,

I am trying to convert a date in a macro to date9 so that I can use it in a function to add or subtract days from it.

Here is the code:

%Macro Calc_work_days (start_date_in , days_in ,direction_in );

%put &start_date_in;
%put &days_in;
%put &direction_in;

/* This routine will add or subtract a specified number of days (ie: days_in) to a date (ie: start_date). */
/* It will skip all weekend days - Saturdays and Sundays */
%LET v_counter = 0;

%let v_new_date = %sysfunc(inputn(%sysfunc(compress(&start_date_in, "'")), mmddyy10.), date9.);

%put v_new_date outside = &v_new_date;
%put days_in = &days_in;

%LET v_new_bus_date = %sysfunc(intnx('day',&v_new_date,&direction_in));
%put v_new_bus_date inside = &v_new_bus_date;
%LET v_day_number = %sysfunc(weekday(&v_new_date.));
%put v_day_number inside = &v_day_number;

%mend Calc_work_days;

%Calc_work_days ('11/29/2022',1,-1 );

And the log which shows the error:

'11/29/2022'
1
-1
v_new_date outside = 29NOV2022
days_in = 1
ERROR: Argument 2 to function INTNX referenced by the %SYSFUNC or %QSYSFUNC macro function is not
a number.
ERROR: Invalid arguments detected in %SYSCALL, %SYSFUNC, or %QSYSFUNC argument list. Execution
of %SYSCALL statement or %SYSFUNC or %QSYSFUNC function reference is terminated.
v_new_bus_date inside = .
ERROR: Argument 1 to function WEEKDAY referenced by the %SYSFUNC or %QSYSFUNC macro function is
not a number.
ERROR: Invalid arguments detected in %SYSCALL, %SYSFUNC, or %QSYSFUNC argument list. Execution
of %SYSCALL statement or %SYSFUNC or %QSYSFUNC function reference is terminated.
v_day_number inside = .
NOTE: Remote submit to EMSVR complete.

Any help would be appreciated!

[Tom · Accepted solution]

The string 29NOV2022 is no more a date value than the string '11/29/2022' you started with.

Either don't bother to format the value in the macro variable so it just represents the number of days since 1960 directly.

%let start_date_in ='11/29/2022';
%let direction_in=-1;

%let v_new_date = %sysfunc(inputn(%scan(&start_date_in,1,'"'), mmddyy10.));
%let v_new_bus_date = %eval(&v_new_date + &direction_in);

509  %put &=v_new_date ;
V_NEW_DATE=22978
510  %put &=v_new_bus_date ;
V_NEW_BUS_DATE=22977

Also notice that there is no need to use INTNX() to adjust a date by days.  Dates are stored as days. Just use arithmetic.

Or if you do then use the formatted value to generate a date literal by adding the quotes and the letter d.

%let v_new_date = %sysfunc(inputn(%scan(&start_date_in,1,'"'), mmddyy10.),date9.);
%LET v_new_bus_date = %sysfunc(intnx(day,"&v_new_date"d,&direction_in),date9.);

514  %put &=v_new_date ;
V_NEW_DATE=29NOV2022
515  %put &=v_new_bus_date ;
V_NEW_BUS_DATE=28NOV2022

Note that here the use of INTNX() is convenient since it allows the using  of the second argument to %SYSFUNC() to format the results.

Also note that you do not need to add quotes around strings like the DAY argument to the INTNX() function when using macro code.  Everything is a string to the macro processor so there is no need to add quotes to make it treat the value like a string.  It is when you want it to treat the string like a name that you have to do something special.  Use an & to indicate macro variable reference or a % to indicate a macro call.

[kissybean]

Thank you so much for your help!  That worked for what I need to do.  🙂

[PaigeMiller]

I am trying to convert a date in a macro to date9 so that I can use it in a function to add or subtract days from it.

Unnecessary. SAS always uses the un-formatted values to perform arithmetic, not matter what the format, so dates in any format can be added or subtracted from one another. Thus, for most of what you are doing, you can work with un-formatted values.

%Macro Calc_work_days (start_date_in , days_in ,direction_in );
/* Convert &start_date_in to an actual date value -- unformatted */
%let start_date_in1 = %sysfunc(inputn(&start_date_in,date9.));

%LET v_new_bus_date = %sysfunc(intnx(day,&start_date_in1,&direction_in));
%put &=v_new_bus_date %sysfunc(putn(&v_new_bus_date,date9.));
%LET v_day_number = %sysfunc(weekday(&start_date_in1));
%put &=v_day_number;

%mend Calc_work_days;
options mprint symbolgen;
%Calc_work_days(29NOV2022,1,-1 )

Note you only need formats when humans have to read the dates, and you only need informats when humans provide the dates (as 29NOV2022). Also, in the macro processor, quotes are often unnecessary (and wrong) and should be avoided when possible.

[ballardw]

Don't format the date after converting to a date value, just use the numeric part. Especially if you want to use any of the functions like INTNX or INTCK. Formatted values are text that cannot be used directly with the functions but must be transformed back to the numeric version and just cause headaches. Also, most parameters for functions such a 'DAY' in intnx do not require the quotes because the macro language will assume they are text and the quotes cause error.

You should copy and paste code into a code box opened on the forum with the </> icon. The main message window will reformat text and the version I got when copying your code from the message window had some characters that were creating errors.

Please see:

%Macro Calc_work_days (start_date_in , days_in ,direction_in );

   %put &start_date_in;
   %put &days_in;
   %put &direction_in;

   /* This routine will add or subtract a specified number of days (ie: days_in) to a date (ie: start_date). */
   /* It will skip all weekend days - Saturdays and Sundays */
   %LET v_counter = 0;

   %let v_new_date = %sysfunc(inputn(%sysfunc(compress(&start_date_in, "'")), mmddyy10.));

   %put v_new_date outside = &v_new_date;
   %put days_in = &days_in;

   %LET v_new_bus_date = %sysfunc(intnx(day,&v_new_date.,&direction_in.));
   %put v_new_bus_date inside = &v_new_bus_date;
   %let formatted_bus_date=%sysfunc(putn(&v_new_bus_date,date9.));
   %put formatted_bus_date= &formatted_bus_date;

   %LET v_day_number = %sysfunc(weekday(&v_new_date.));
   %put v_day_number inside = &v_day_number;

%mend Calc_work_days;


%Calc_work_days ('11/29/2022',1,-1 );

I added an additional formatted value to show that the result of the intnx is working.

Note that if you intend to skip weekends then you want to use the interval Weekday in the INTNX function.

And you could save some headaches by not including quotes on the startdate_in parameter. Then you won't need the compress part.

[kissybean]

Thank you for you help.  I appreciate it!