Recoding Date using Query Builder

[Kaiyyum02]

Hi I'm actually a new user to SAS Enterprise Guide. My issue here is that I wanted to change my dates that is 01JAN2012

to 01JAN2017 using Query Builder. I tried recoding through using query builder but it just doesn't seem to work. Anyone knows any functions that I can use to soleve this? Thanks!

[VDD]

Are you always adding 5 years to the date?

[Kaiyyum02]

Yes I need to add 5 years to it

[Kaiyyum02]

Hi, thanks for replying but actually I need to change all of the dates that are in this column . There are different dates in this row of mine but all of the YEAR from the dates in this row is 2012 and I need to change all of it to 2017 and that they are in the same column. How do I do this? Ouh and by the way I've tried out the expression that you gave but it gave the value as 01JAN1960 instead as stated in CAPTURE02 of the file I attached.How do I do this?

[Kurt_Bremser]

---

@Kaiyyum02 wrote:

Hi I'm actually a new user to SAS Enterprise Guide. My issue here is that I wanted to change my dates that is 01JAN2012

to 01JAN2017 using Query Builder. I tried recoding through using query builder but it just doesn't seem to work. Anyone knows any functions that I can use to soleve this? Thanks!

---

Use the intnx() function:

newdate = intnx('year',olddate,5);

[Reeza]

INTNX()

SUM()

SAS dates are stored as numbers, which means you can just add values to increment. However, since months and years do not have a standard amount of days, INTNX() is more useful to increment a date. Keep an eye on the fourth parameter which will allows you to align a date to the start, end or specific day of the month. 

---

@Kaiyyum02 wrote:

Hi I'm actually a new user to SAS Enterprise Guide. My issue here is that I wanted to change my dates that is 01JAN2012

to 01JAN2017 using Query Builder. I tried recoding through using query builder but it just doesn't seem to work. Anyone knows any functions that I can use to soleve this? Thanks!

---

[Kaiyyum02]

Thanks for replying. I've attached a screenshot of the outcome of the FUNCTION that you recommend to use but actually I need to convert the YEAR of all of the dates that are in this column. There are different dates in this column but the YEAR of all the dates in this column are 2012. I need to change the YEAR of all the dates into 2017 so how do I do this? When I use the FUNCTION that you recommend which is INTNX() SAS gave out the value of 01JAN1960 instead. The INTNX function can only add values to a single particular date at a time right? May I ask if the function can be used on multiple dates at one time and if so, can you tell me how to type out the expression to do so. If not, then what other alternatives are there?

Thanks!

[Kurt_Bremser]

---

@Kaiyyum02 wrote:

Thanks for replying. I've attached a screenshot of the outcome of the FUNCTION that you recommend to use but actually I need to convert the YEAR of all of the dates that are in this column. There are different dates in this column but the YEAR of all the dates in this column are 2012. I need to change the YEAR of all the dates into 2017 so how do I do this? When I use the FUNCTION that you recommend which is INTNX() SAS gave out the value of 01JAN1960 instead. The INTNX function can only add values to a single particular date at a time right? May I ask if the function can be used on multiple dates at one time and if so, can you tell me how to type out the expression to do so. If not, then what other alternatives are there?

Thanks!

---

Read the log (Maxim 2) and look for unusual messages like type conversions, invalid values etc.

For detailed help, post example data in usable form (data step with datalines, see my footnotes), so we can write code for it.

[TomKari]

One thing we need to figure out is what style your date is. SAS has two "date-like" storage mechanisms; one is number of days, and one is number of seconds. The functions and formats need to be used taking this into account.

Set up a new query on your table, and include only your date column. Include it three times. On the first, use a format of

best15.

On the second, use a format of

date.

On the third, use a format of

datetime.

Post a few lines of the result, and we'll know what your data is.

Tom