INTNX

[Thalitacosta]

I have a code with the following function:

put(input(put(%intnx2(month,&mesref.,0,e),8.),yymmdd8.),ddmmyys8.) as DT_PROD

How to resolve the error?

WARNING: Apparent invocation of macro INTNX2 not resolved.
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant,
a missing value, (, +, -, BTRIM, CALCULATED, CASE, EXISTS, INPUT, NOT, PUT, SUBSTRING, TRANSLATE, USER, ^, ~.

ERROR 200-322: The symbol is not recognized and will be ignored.

[Kurt_Bremser]

You try to use a macro which has not been defined.

From the looks, I suspect this code wants to use INTNX.

But why do you first PUT a date value as its raw value (count of days), then INPUT it with YYMMDD8. (ehich simply cannot work), only to then PUT it as a string which is unusable for further processing?

This will work

intnx('month',&mesref.,0,'e') as dt_prod format=ddmmyys10.

(assuming that the macro variable mesref contains a valid date value)

[Quentin]

As written, the code shows an attempt to call a macro named INTNX2, and that macro does not exist.

Are you attempting to invoke a macro, or are you intending to call the INTNX() function?

Can you describe a bit more about what you are trying to do?  What is the value of the macro variable MESREF?  Meaning, what do you get in the log if you submit:

%put &=mesref ;

I'm guessing it date of some sort, but the format matters.

What value are you hoping to assign to DT_PROD?

[Thalitacosta]

MESREF = 202306 (numeric) has the format yyyymm, I want the format dd/mm/yy (DT_PROD = 30/06/23 (character)), in which dd is the last day of the month (end):

I have

%let mesref=202306;

and do 

put(input(put(intnx('month',&mesref.,0,'e'),8.),yymmdd8.),ddmmyys8.) as DT_PROD,

but dt-prod has only obs missing.

[PaigeMiller]

I think you have overcomplicated this tremendously.

Try this:

%let mesref=202306;

data want;
    dt_prod = input("&mesref",yymmn6.); /* Convert &MESREF to actual SAS date */
    dt_prod = intnx('month',dt_prod,0,'e'); /* Convert to last day of month */
    format dt_prod ddmmyys10.; /* Assign proper date format */
run;

The advice we give to lots of people is to first write code that works without macro language first. Once you get that to work, if you must turn it into macro code, it should be a lot easier than just doing the whole thing from scratch in macro language. The same applies to writing multi-step code, get each step to work first before trying to combine it into a single step.

Also, I think in general working with the last day of the month is harder, both to program and to think about, than working with the first day of the month.

[Quentin · Accepted solution]

It looks like you're using SQL?

Storing dates as text strings is making this harder, because SAS doesn't know that 202306 represents a date.  Generally it is better to store dates as dates.  If you want to store the date as a string, and create a new string that represents a different date, you need to first convert your string to a date (using INPUT), then calculate the new date (using INTNX), then convert that value to a string (using PUT).  So you could do it like:

%let mesref=202306 ;

proc sql ;
  select put(intnx('month',input("&mesref",yymmn6.),0,'e'),ddmmyys8.) as want
  from sashelp.class
  ;
quit ;

[Kurt_Bremser]

202306 days from 1960-01-01 are no date which makes sense. You have to convert this text to a usable date first.

And you never store dates as strings as they can't be used in any meaningful way (e.g. for calculations or sorting).

intnx('month',input("&mesref.",yymmn6.),0,'e') as DT_PROD format=ddmmyys10.,

[Tom]

The most obvious issues is you are call some macro named %INTNX2() that it looks like you did not define.

But let's look at the whole expression and try and figure out what it might be trying to do.

put(input(put(%intnx2(month,&mesref.,0,e),8.),yymmdd8.),ddmmyys8.) as DT_PROD

The first thing that stands out is that 

as DT_PROD

So this is only going be useful as part the variable list of an SQL SELECT statement.

Next let's take that Dagwood sandwich of function calls apart.

On the outside you have a PUT() function call that is using the DDMMYYS8 format.  So that will take as input a DATE value (number of days numbering from 1960) and generate string that is 8 bytes long and has slashes between the Day Month and Year parts.  So there is big problem there already.  There is no room to store the century part of year value.  So there will be no way to tell the difference between 01AUG2023 and 01AUG1923.  You should use a length of 10 if you want to include the delimiters.

The next layer in is using the INPUT() function with the YYMMDD8. informat.  So that will take the first 8 characters from a string and try to read it as a date value in Year Month Day order.   So that seems to match the outer layers need for a date value as its input.

The next layer is using the PUT() function to write something (the result of mysterious %INTNX2() macro) using the 8. format.  So that means that the %INTNX2() macro needs to generate a number that is an integer between -9,999,999 and 99,999,999 so that it will fit into 8 digits.  So this looks like a problem.   If the %INTNX2() macro is doing something similar to the INTNX() function then it should return a DATE value when using the MONTH interval.  But if you write the date value for the data 31DEC2021 using the 8. format you will get the string '   22645'.  Which if you try to read it using the YYMMDD informat will result in a missing value since there is no month with 45 days nor any year with 64 months.

If the macro variable MESREF has either the name of a variable that has date values.  Or has a string that will look like a date value to SAS.  Such as the string 22645 or "31DEC2021"d then perhaps you just want call the actual INTNX() function there?  

But in that case you can remove two layers from your sandwich and just write:

put(intnx('month',&mesref.,0,'e'),ddmmyys10.) as DT_PROD

to create DT_PROD as a character variable.

If instead you want to create DT_PROD as a numeric variable with a date value then you can remove another layer and just use:

intnx('month',&mesref.,0,'e') as DT_PROD format=ddmmyy10.

Although you should avoid using either DMY or MDY ordering of date strings.  Either choice will confuse 50% of your audience.  You could use the YYMMDD10. format or the DATE9. format and avoid the confusion.

So perhaps you want:

intnx('month',&mesref.,0,'e') as DT_PROD format=yymmdds10.