DATA Step, Macro, Functions and more

Shifting a datetime value by a number of years

Reply
Contributor
Posts: 28

Shifting a datetime value by a number of years

I received a dataset in which many of the observations have a datetime value where the year is 2076 or 2077. Obviously these values are off by 60 years. So how can I modify the year value in that datetime variable so that the right year is in there.

 

ex. DOB = 02/15/2076 13:00 should be 02/15/2016 13:00

     DOB = 05/26/2077 9:35 should be 05/26/2017 9:35

 

Thanks!

Andrew

Andrew A. Kramer, PhD
President
Prescient Healthcare Consulting
Super User
Posts: 19,855

Re: Shifting a datetime value by a number of years

Posted in reply to DocMartin

Use INTNX.

Contributor
Posts: 28

Re: Shifting a datetime value by a number of years

That does not work. When I try newdob = intnx("year", dob, -60) all I get is 1/1/1960. Plus, I need the time portion to remain stable. 

Andrew A. Kramer, PhD
President
Prescient Healthcare Consulting
Super User
Posts: 19,855

Re: Shifting a datetime value by a number of years

Posted in reply to DocMartin

You stated you had a datetime variable? If so, then Year is not the correct interval, it's DTYEAR I think. For the remaining values to be the same use the fourth parameter which controls the alignment. 

 

newdob = intnx("dtyear", dob, -60, 's')

 

 

Trusted Advisor
Posts: 1,931

Re: Shifting a datetime value by a number of years

Posted in reply to DocMartin

DocMartin wrote:

That does not work. When I try newdob = intnx("year", dob, -60) all I get is 1/1/1960. Plus, I need the time portion to remain stable. 


You have a datetime value, not a date value.

 

Use

intnx("dtyear",dob,-60)
Super User
Posts: 19,855

Re: Shifting a datetime value by a number of years

Posted in reply to DocMartin

Did you import this data from Excel originally?

 

Contributor
Posts: 28

Re: Shifting a datetime value by a number of years

Yes, unfortunately.
Andrew A. Kramer, PhD
President
Prescient Healthcare Consulting
Super User
Posts: 19,855

Re: Shifting a datetime value by a number of years

Posted in reply to DocMartin

DocMartin wrote:
Yes, unfortunately.

Assuming this means you did import it from Excel, then I would suggest an approach noted here:

https://communities.sas.com/t5/Integration-with-Microsoft/Importing-date-time-variables-from-Excel-i...

 

Or 

 

There's a specific value that you need to use to account for Excel date to SAS date but I can't remember the rule, if you're sure it's 60 years this appraoch will work though.

Ask a Question
Discussion stats
  • 7 replies
  • 76 views
  • 0 likes
  • 3 in conversation