SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Correctly converting to DATETIME20.?

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 90
Accepted Solution

Correctly converting to DATETIME20.?

The LAST_UPDATED variable has the format DATETIME20. and appears as this: 05OCT2012:00:00:00

I've got two date variables from this month that I need to be able to compare with LAST_UPDATED, but have trouble setting them up in the correctly. For some reason, when I use the DATETIME20. format for them, they appear like this:

01JAN1960:05:21:15

01JAN1960:05:21:08

Both values are from October this year. How do I "translate them correctly" to work as DATETIME20. values?

This is the code that generates the current variables:

%let man_nylig = %sysfunc(intnx(week1.3,%sysfunc(today()),0,b),DATETIME20.);

%let tir_forrige = %sysfunc(intnx(week1.3,%sysfunc(today()),-1,b),DATETIME20.);

They appear as correct recent dates when I use other formats, such as nordfde9.

Thanks.


Accepted Solutions
Solution
‎10-11-2012 07:39 AM
Super User
Posts: 5,424

Re: Correctly converting to DATETIME20.?

Posted in reply to EinarRoed

It seems that you are confusing SAS date and datetime concepts.

A SAS date contains the number of days since 1/1 1960.

A SAS datetime contains number of seconds since 1/1 1960.

It seems that you don'r have thje time part of man_nylig and tir_forrige. I guessing that you need to see if last_update is between those two.

Either use datepart() function on last_updated, or add some time constants to the other two.
%let man_nylig = %sysfunc(intnx(week1.3,%sysfunc(today()),0,b),DATE.):23:59:59;

Data never sleeps

View solution in original post


All Replies
Frequent Contributor
Posts: 90

Re: Correctly converting to DATETIME20.?

Posted in reply to EinarRoed

I figured it might work to add "dt" to the week variable, but it didn't. I've tried countless other things, as well.

Basically my problem is: How do I transform raw time data, such as 19275 and 19268 (which I get if I don't include the DATETIME20-part in the code above), into functioning DATETIME20 in a macro? The current code seems to only add the raw time data into the time-part of datetime. I can resolve this if working in a DATA step, but I can't make it work in a macro.

Solution
‎10-11-2012 07:39 AM
Super User
Posts: 5,424

Re: Correctly converting to DATETIME20.?

Posted in reply to EinarRoed

It seems that you are confusing SAS date and datetime concepts.

A SAS date contains the number of days since 1/1 1960.

A SAS datetime contains number of seconds since 1/1 1960.

It seems that you don'r have thje time part of man_nylig and tir_forrige. I guessing that you need to see if last_update is between those two.

Either use datepart() function on last_updated, or add some time constants to the other two.
%let man_nylig = %sysfunc(intnx(week1.3,%sysfunc(today()),0,b),DATE.):23:59:59;

Data never sleeps
Frequent Contributor
Posts: 90

Re: Correctly converting to DATETIME20.?

Thank you very much! Smiley Happy

Regular Contributor
Posts: 151

Re: Correctly converting to DATETIME20.?

Posted in reply to EinarRoed

Your original thought of putting 'dt' in front of 'week1.3' is close, however you also need to change today() (the current date) to datetime() (the current datetime).  Applying both these changes will make your code work.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 925 views
  • 3 likes
  • 3 in conversation