Why Datetime Macro Variable wont convert to Character

HCA_Risk 0
Viewed 23

All,

I am trying to nest a macro within a macro but am unsuccessful. The Start_Cycle variable is set every few months and updated manually. I want to create a start_point variable that goes back 6 months and I successfully created it, however, the output includes a space after the %STR as seen below

%let start_cycle = '01JUL2022:00:00:00'dt; /set to beginning month of this cycle/

%let start_point = %STR(%')%sysfunc(intnx(DTMONTH,&start_cycle.,-6,b),datetime19.)%STR(%')dt;

%put &start_point;

Output below

%let start_cycle = '01JUL2022:00:00:00'dt; /set to beginning month of this cycle/

%let start_point = %STR(%')%sysfunc(intnx(DTMONTH,&start_cycle.,-6,b),datetime19.)%STR(%')dt; %put &start_point; ' 01JAN2022:00:00:00'dt

^^Does anyone know why there is a space after the single quote? ' 01JAN2022:00:00:00'dt

Since it runs without issues, I decided to create another macro variable that does the same thing, but instead, the output needs to be converted to a character string in this format below (current Macro)

%let start_pointSales = '2022/01';

I tried multiple times using different ways of going about this, spent many hours looking through forum from SAS Communities to StackOverflow and even SAS youtube videos to no luck. Anyone have any luck in combating this?

To-Be Macro:

%let NEW_start_pointSales = %sysfunc(intnx(month,&start_cycle.,-6,b),yymms.);

%put &NEW_start_pointSales;

The NEW_start_pointSales will be used in the WHERE clause with Data type Varchar (21) using PROC SQL.

left join EDWSALE.DSCOE_LLD (where=( &NEW_start_pointSales. <= SALES_MONTH < &end_pointSales.

Output Error below:

NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24         
25         GOPTIONS ACCESSIBLE;
WARNING: An argument to the function INTNX referenced by the %SYSFUNC or %QSYSFUNC macro function is out of range.
NOTE: Mathematical operations could not be performed during %SYSFUNC function execution. The result of the operations have been set 
      to a missing value.

Any help is appreciated!

sas-macro sas datetime
1 Answers

You cannot apply a format, YYMMS, designed to work on DAYS to a value that is in SECONDS. If you want to use a date format on a datetime value you need to convert the value from seconds to days. You could use the DATEPART() function or just divide by the number of seconds in a day.

Why are you trying to compare a variable that is CHARACTER to numbers? If you generate a macro variable with a string like 2022/01 and then use it to generate code like:

&NEW_start_pointSales. <= SALES_MONTH

that will result in code like:

2022/01 <= SALES_MONTH

which is comparing the number 2022 (any number divided by 1 is itself) to the value SALES_MONTH, which you just said was a character string.

What types of strings does SALES_MONTH contain? That will determine how (or whether) you can make inequality tests against it.

PS There is space in the output of DATETIME19 because that is how that particular format works. Note that there is a bug in the DATETIME format and you cannot use DATETIME18. to produce a string with four digits for the year even though only 18 characters would be used. The extra space does not matter to using the string in a datetime literal as the DATETIME informat will recognize the string even with the extra space.

user7NPk0
Related