topic Re: intnx syntax error in SAS Programming

intnx syntax error

JJP1 — Mon, 25 Nov 2019 14:42:39 GMT

Hi,Iam running below code and iam getting syntax error.
please help where iam going wrong please.

proc sql;
   create table work.W5V4AFV as
      select
         num,year,month
   from test
      where NOT 
         ( 
         year=(intnx(month,(today()),-1),year4.)/*i need 2019 */
         and
         month=(putn((intnx(month,(today()),-1),month2.),z2.))/* i need 10 value for month */
         )
         
          & 
         
         MDY(month,1,year) >= (intnx(month,(today()),-12,same),date9.) /*12 monrhs ago date */
   ;
quit;

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

34 and
35 month=(putn((intnx(month,(today()),-1),month2.),z2.))
_ _ _
79 22 22
200
ERROR 79-322: Expecting a ).

ERROR 22-322: Syntax error, expecting one of the following: a name, *.

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

36 )
37
38 &

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

34 and
35 month=(putn((intnx(month,(today()),-1),month2.),z2.))
_ _ _
79 22 22
200
ERROR 79-322: Expecting a ).

ERROR 22-322: Syntax error, expecting one of the following: a name, *.

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

36 )
37
38 &

Re: intnx syntax error

PeterClemmensen — Mon, 25 Nov 2019 14:59:06 GMT

your problem (there may be others) is this line

year=(intnx(month,(today()),-1),year4.)

Either you want a Put(n) kind of function before the intnx, or you have to drop the year4. part.

Re: intnx syntax error

PaigeMiller — Mon, 25 Nov 2019 15:02:06 GMT

Typically, you would use

intnx('month',...)

where month is surrounded by single quotes (or double quotes, it doesn't matter here). Whether or not this fixes your problem or there are other problem, I don't know.

In the future, please be so kind as to include the entire log (not just the error messages) of this step by clicking on the {i} icon and pasting the log into the window that appears. This preserves formatting in the log and makes it easier to read.

Re: intnx syntax error

Amir — Mon, 25 Nov 2019 15:09:26 GMT

There appears to be two formats to be applied, viz. month2. and z2., but there is only one putn():

month=(putn((intnx(month,(today()),-1),month2.),z2.))

Have you tried using put() another time between the first contiguous open brackets "((":

month=(putn(put(intnx(month,(today()),-1),month2.),z2.))

Regards,

Amir.

Re: intnx syntax error

Tom — Mon, 25 Nov 2019 15:14:03 GMT

What does your variable MONTH contain? You are using it in the INTNX() as if it was a character variable that contains the type of interval that you want to increment your date by. Like MONTH, DAY, YEAR, etc.

If you want the year from a date use the YEAR() function, similar for month use the MONTH() function.

proc sql;
   create table work.W5V4AFV as
      select
         num,year,month
   from test
   where NOT 
       ( year=year(intnx('month',today(),-1))
     and month=month(intnx('month',today(),-1))
       )
    and MDY(month,1,year) >= intnx('month',today(),-12,'same')
   ;
quit;

Re: intnx syntax error

ballardw — Mon, 25 Nov 2019 15:48:30 GMT

Since YEAR and MONTH are supposed to be in your data set, (the way they appear on the SELECT statement) then the question becomes are they numeric or character values?

Of If the variables Year and Month are NOT already in the data set then you have a LOT of basic SQL issues to address.

Such as do you want Year and Month to be numeric or character variables?

At this point I strongly suggest posting an example of your existing data and what you expect for an output for that given data.

Re: intnx syntax error

JJP1 — Tue, 26 Nov 2019 07:04:31 GMT

Hi All,sorry for confusion.
Iam running below code to create year and month which is giving desired values what i need for year and month.
but i don't want to use "%sysfunc".i tried by removing the %sysfunc.i am constantly getting the posted syntax error.please help

proc sql;
   create table work.W5WITF2 as
      select
         Num length = 10,
         
         (%sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()),-1),month2.),z2.))) as Month length = 3
            format = Z2.
            label = 'Month',
         (%sysfunc(intnx(month,%sysfunc(today()),-1),year4.)) as  length = 3
            format = Z4.
            label = 'Year'
         
   from work.W5WITF2;

   ;
quit;

Re: intnx syntax error

andreas_lds — Tue, 26 Nov 2019 07:28:51 GMT

You need to understand the differences between the value of a variable and the displayed (aka formatted) value. A numeric variable doesn't have leading zeros, but the format z2 can be used to display the variable with leading zeros.

The following step seems to create what you want:

data work.W5WITF2;
   length Month Year LastMonth 8;
   drop LastMonth;

   format 
      Month z2. 
      Year z4. /* useless, because the year has almost always four digits */
   ;

   LastMonth = intnx('month', today(), -1);
   Month = month(LastMonth);
   Year = year(LastMonth);
run;

@JJP1 wrote:

Hi All,sorry for confusion.
Iam running below code to create year and month which is giving desired values what i need for year and month.
but i don't want to use "%sysfunc".i tried by removing the %sysfunc.i am constantly getting the posted syntax error.please help
proc sql;
   create table work.W5WITF2 as
      select
         Num length = 10,
         
         (%sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()),-1),month2.),z2.))) as Month length = 3
            format = Z2.
            label = 'Month',
         (%sysfunc(intnx(month,%sysfunc(today()),-1),year4.)) as  length = 3
            format = Z4.
            label = 'Year'
         
   from work.W5WITF2;

   ;
quit;

Re: intnx syntax error

JJP1 — Tue, 26 Nov 2019 10:15:24 GMT

Thanks @andreas_lds ,

iam following as you suggetsed and ran below code,for month it is working as expected,but year iam getting 1960.i need year value should be 2019 please(last month's year).please help

data t;
h=month(intnx('month', today(), -1));
y=year(h);
run;

Ouput coming as below :

10 1960

Needed Ouput should be

10 2019

i tried below option also(actually last_month and month and i need to do it in single line code please,please help )

data work.W5WITF2;
   length Month Year LastMonth 8;
   drop LastMonth;

   format 
      Month z2. 
      Year z4. /* useless, because the year has almost always four digits */
   ;

   
   Month = month(intnx('month', today(), -1));
   Year = year(Month);
run;

Re: intnx syntax error

JJP1 — Tue, 26 Nov 2019 12:54:41 GMT

Hello all,

can anyone help please

Re: intnx syntax error

andreas_lds — Tue, 26 Nov 2019 13:18:16 GMT

The functions year and month expect a date as parameter and return the number of year/month, so you can't use the value returned by the function month as parameter for year.

Re: intnx syntax error

JJP1 — Tue, 26 Nov 2019 13:29:05 GMT

Iam sorry i did not understand.i need to create previous_month ,current_yeat to be creating using single line code,
Actually iam using below one line code it is working fine.but i just want to find out other option with out 2 line code please.
you mean it is not possible please ?

%sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()),-1),month2.),z2.))
%sysfunc(intnx(month,%sysfunc(today()),-1),year4.)

Re: intnx syntax error

andreas_lds — Tue, 26 Nov 2019 13:43:09 GMT

@JJP1 wrote:

Iam sorry i did not understand.i need to create previous_month ,current_yeat to be creating using single line code,
Actually iam using below one line code it is working fine.but i just want to find out other option with out 2 line code please.
you mean it is not possible please ?
%sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()),-1),month2.),z2.))
%sysfunc(intnx(month,%sysfunc(today()),-1),year4.)

If you want to set two variables (month and year) you will have to use two statements. You have posted two lines of code, so i don't understand the requirement of having all in a single line. Maybe we should get back to the beginning: what do you really need as result?

Re: intnx syntax error

Tom — Tue, 26 Nov 2019 13:56:40 GMT

@JJP1 wrote:

Iam sorry i did not understand.i need to create previous_month ,current_yeat to be creating using single line code,
Actually iam using below one line code it is working fine.but i just want to find out other option with out 2 line code please.
you mean it is not possible please ?
%sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()),-1),month2.),z2.))
%sysfunc(intnx(month,%sysfunc(today()),-1),year4.)

Why are you posting macro code on this thread?

Do you want to generate one macro variable?

Do you want to generate two macro variables?

How are you going to use those generated values to help you generate actual SAS code?

Re: intnx syntax error

Tom — Tue, 26 Nov 2019 13:58:59 GMT

You asked the YEAR() function to extract the year from a date value of 10. Since SAS counts days from 1960 the number 10 is the date '10JAN1960'd. So it did exactly what you asked it to do.

Re: intnx syntax error

Tom — Tue, 26 Nov 2019 14:15:52 GMT

@JJP1 wrote:

Hi All,sorry for confusion.
Iam running below code to create year and month which is giving desired values what i need for year and month.
but i don't want to use "%sysfunc".i tried by removing the %sysfunc.i am constantly getting the posted syntax error.please help
proc sql;
   create table work.W5WITF2 as
      select
         Num length = 10,
         
         (%sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()),-1),month2.),z2.))) as Month length = 3
            format = Z2.
            label = 'Month',
         (%sysfunc(intnx(month,%sysfunc(today()),-1),year4.)) as  length = 3
            format = Z4.
            label = 'Year'
         
   from work.W5WITF2;

   ;
quit;

%SYSFUNC() is a macro function that allows you to call a normal SAS function in macro code. Once the macro processor finishes evaluating the macro expression the resulting text is passed onto SAS to process. One way to help you debug the code is to remove the macro code and just replace it with the text it would generate and then you can debug that code. Once you have the SAS code debugged you can replace the text with the macro code that generates the text and start debugging the macro aspects of your code.

So here is your code with the macro function call replaced with representative values. I also cleaned up the formatting a little to make it a little easier to read.

proc sql;
create table work.W5WITF2 as
  select 
    Num length = 10
  , (10) as Month length = 3 format = Z2. label = 'Month'
  , (2019) as  length = 3 format = Z4. label = 'Year'
  from work.W5WITF2
;
quit;

Let's review the three variables you are defining in the SELECT clause.

First you are taking the variable NUM and changing its length to 10. So NUM must be a character variable that is in the dataset WSWITF2 since the maximum length that SAS can use to store a numeric variable is the full 8 bytes that the floating point numbers SAS uses require.

Next you are storing the number 10, the extra parentheses around the value do not change it, and are calling MONTH. You are telling SAS to display it as two digits with leading zeros. You are telling it that the label is the same as the name (why attach a label if it is just going to have the same value as the name?). You are also telling it remove the 5 least significant bytes from the value when storing it into the dataset. That should be fine if the value is only ever the integers 1 to 12.

Next you are trying to do the same thing with the number 2019, but in this case you forgot to give SAS name for this variable.