Help with INTNX function

[marleeakerson]

Hello, 

I am trying to create new date variables that are 1) 9 months before and 2) 3 months after a certain date variable. 

For example, if I had a variable called date1 with date in the format of mm/dd/yyyy, and I wanted two new date functions, date2 and date3, where date2=date1-9months and date3=date1+3months how would I do this using the INTNX function? 

Thank you!

[Tom]

Note that If you have a date value it does not matter what format you are using to display it.  If you don't have a date value then first convert your string with the slashes into a date value.

The INTNX() function is what you want to use.  How did you use it? In what way did it not work?

Make sure to use date values as the input and use the MONTH interval. You also need to decide if you want to move to the same day in the new month (or the beginning, end or middle).

date2=intnx('month',date1,-9,'same');
date3=intnx('month',date1,3,'same');

[marleeakerson]

Thank you! Can I just use 'date1' as the input if I want the date2 and date3 variables to be dependent on that date1 variable? Or do I have to write in a specific date?

[Tom]

The INTNX() function , just like any other function, just wants the values it needs to operate on.  Whether you provide the value as a literal/constant value, a variable reference or an expression does not matter, as long as it has the type of value that is needed by the function.

So if you already have a dataset, let's call it HAVE, which has a variable named DATE1 then you could run a data step like this to make a new dataset , let's call it WANT, that adds the two new variables.  Make sure to attach a date style format to the new variables so they print the number of days in a human recognizable way.

data want;
  set have;
  date2=intnx('month',date1,-9,'same');
  date3=intnx('month',date1,3,'same');
  format date1 date2 date3 yymmdd10.;
run;

[marleeakerson]

For some reason the date2 and date3 variables are just showing up as empty values (the entire column is just periods). Do you know why this is?

[Tom]

Either the syntax for your assignment statement is wrong.  Or the DATE1 variable does not actually have date values.  If it is not empty it might have character strings. It might have numbers that look to you like a date, for example you have then number 20,190,109 and think that means the date '09JAN2019'd,  Or you might have a datetime value, like '09JAN2019:00:00:00'dt.  

You could try first to convert the DATE1 values to actual date values, the method will depend on what you actually have.

If you have datetime values instead you could create the new variables as datetime values also by using the DTMONTH interval instead of the MONTH interval.  Make sure to attach a datetime format to the new variables instead of a date format.  Trying to display a datetime value (number of seconds) with a date format will cause it to think the date is many years into the future since there are 24*60*60 seconds in each day.

[marleeakerson]

Yes it appears my DATE1 function is a character variable not a date variable. How do I change it from character to a sas date format?

[Tom]

You use an INFORMAT to convert text into values (just like you attach format to a variable to tell SAS how to convert the values into text).  You can use the INPUT() function (or the INPUTN() function) with an appropriate informat.  SAS has many informats that generate date values, so use one that matches the style that your strings have.  So if you strings have the parts of the date in month day year order you could use the MMDDYY informat.

 date2=intnx('month',input(date1,mmddyy10.),-9,'same');
 date3=intnx('month',input(date1,mmddyy10.),-9,'same');
 format date2 date3 yymmdd10. ;

[marleeakerson]

Yes that worked! Thank you so so much for all your help!