topic Re: Recoding Date using Query Builder in SAS Data Management

Recoding Date using Query Builder

Kaiyyum02 — Tue, 25 Dec 2018 16:33:31 GMT

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!

Re: Recoding Date using Query Builder

VDD — Tue, 25 Dec 2018 16:49:19 GMT

Are you always adding 5 years to the date?

Re: Recoding Date using Query Builder

Kurt_Bremser — Tue, 25 Dec 2018 17:08:52 GMT

@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);

Re: Recoding Date using Query Builder

Reeza — Tue, 25 Dec 2018 19:53:48 GMT

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!

Re: Recoding Date using Query Builder

Kaiyyum02 — Wed, 26 Dec 2018 00:19:46 GMT

Yes I need to add 5 years to it

Re: Recoding Date using Query Builder

Kaiyyum02 — Wed, 26 Dec 2018 00:48:07 GMT

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?

Re: Recoding Date using Query Builder

Kaiyyum02 — Wed, 26 Dec 2018 01:01:12 GMT

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!

Re: Recoding Date using Query Builder

Kurt_Bremser — Wed, 26 Dec 2018 08:06:12 GMT

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

Re: Recoding Date using Query Builder

TomKari — Wed, 26 Dec 2018 17:25:47 GMT

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