intnx syntax error

[JJP1]

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 &

[PeterClemmensen]

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.

[PaigeMiller]

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.

[Amir]

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.

[Tom]

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;

[ballardw]

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.

[JJP1]

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;

[andreas_lds]

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;

---

[JJP1]

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;

[JJP1]

Hello all,

can anyone help please

[andreas_lds]

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.

[JJP1]

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.)

[andreas_lds]

---

@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?

[Tom]

---

@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?

[Tom]

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.